[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 569
  • Last Modified:

Vlookup multiple columns

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
Rayne
Asked:
Rayne
  • 3
1 Solution
 
barry houdiniCommented:
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
 
RayneAuthor Commented:
Thank you so much Barry. This works GOLD. Thanks again for the help. Love EE.
0
 
RayneAuthor Commented:
Quick feedback and solution - Great expert
0
 
jkasavanCommented:
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
 
RayneAuthor Commented:
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now