?
Solved

Vlookup multiple columns

Posted on 2012-03-26
5
Medium Priority
?
563 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 2000 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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

719 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