Solved

Vlookup multiple columns

Posted on 2012-03-26
5
557 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
  • 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now