vlookup 2 fields

[cturner]

I have 3 columns of data. Column A is part numbers. Column B is a qty
and Column C is a price. It is sorted alpha and there can be many of
the same part #'s.. What I want to do is look up a part number (lets
say #1) and then my VLOOKUP will only look in the rows that contain 1
as the Part# and lookup the QTY and bring me back a price. For
instace, I have a Part# of 1, and a Qty of 10, so I want to bring pack
the price of 100. I don't know if I need 2 vlookups, and INDEX, a
MATCH or what.

Please help. Chris
Part# qty price
1 2 20
1 5 50
1 10 100
1 20 200
1 30 300
1 50 500
2 2 20
2 5 50
2 10 100
2 20 200
2 30 300
2 50 500

 

[vezerid]

I have 3 columns of data. Column A is part numbers. Column B is a qty
and Column C is a price. It is sorted alpha and there can be many of
the same part #'s.. What I want to do is look up a part number (lets
say #1) and then my VLOOKUP will only look in the rows that contain 1
as the Part# and lookup the QTY and bring me back a price. For
instace, I have a Part# of 1, and a Qty of 10, so I want to bring pack
the price of 100. I don't know if I need 2 vlookups, and INDEX, a
MATCH or what.

Please help. Chris
Part# qty price
1 2 20
1 5 50
1 10 100
1 20 200
1 30 300
1 50 500
2 2 20
2 5 50
2 10 100
2 20 200
2 30 300
2 50 500

Part# in F1, Qty in G1:
=INDEX(C1:C100,MATCH(F1&G1,A1:A100&B1:B100,0))

This is an array formula, thus commit with Shift+Ctrl+Enter.

HTH
Kostis Vezerides

 

[Dave Peterson]

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))