Solved

Vlookup multiple columns

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

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 …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

626 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