Solved

Vlookup multiple columns

Posted on 2012-03-26
5
561 Views
Last Modified: 2012-03-26
Hi,

There are three tables in excel:
The Accountlist table lists all the account codes with the type of price category they are assigned to.
The PriceList item list lists out all merchandise with their corresponding prices.
Ledger lists out the costs for the merchandise purchased by an account.

How can I set up the ledger column (cost) in excel to that it looks up the right price for a merchandise -based on the price category of the corresponding account

Thanks
sampleVL.xlsx
0
Comment
Question by:Rayne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 37767885
Try this formula in D18 copied down

=IFERROR(INDEX(C$4:E$6,MATCH(C18,Items,0),MATCH(VLOOKUP(B18,H$4:I$10,2,0),C$3:E$3,0)),"")

see attached

regards, barry
sample-barry.xlsx
0
 

Author Comment

by:Rayne
ID: 37768050
Thank you so much Barry. This works GOLD. Thanks again for the help. Love EE.
0
 

Author Closing Comment

by:Rayne
ID: 37768058
Quick feedback and solution - Great expert
0
 
LVL 2

Expert Comment

by:jkasavan
ID: 37768568
It is possible if you can reformat the PriceList like this:

item       PriceCategory      ItmCat      Cost
pen               b1        penb1           $1.00
pen               b2        penb2           $2.00
pen               b3        penb3           $4.00
pencil       b1        pencilb1              $3.00
pencil       b2        pencilb2              $4.00
pencil       b3        pencilb3              $9.00
pencilBox       b1        pencilBoxb1  $8.00
pencilBox       b2        pencilBoxb2  $10.00
pencilBox       b3        pencilBoxb3  $15.00

The cells in PriceList column D combine the cells in B and C:

=B4&C4

Then the cells in Ledger column D use nested VLOOKUP:

=VLOOKUP(C18&VLOOKUP(B18,$H$3:$I$7,2,FALSE),$D$3:$E$12,2,FALSE)

See Sheet2 of the attached.
sampleVL.xlsx
0
 

Author Comment

by:Rayne
ID: 37768615
Hi,

Thank you for the feedback, but I will have table for the price list as I indicated. that structure will not change.

Rayne
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question