Double Lookup Without Exact Match-Price Breaks

[shaqattack1992-google]

Hello,

I have a table that looks like this

-----A------------B-----------C-------
Part Number------Qty-------Price Each
1----Part 1---------2-------------23.01
2----Part 1--------40------------8.76
3----Part 1--------80-----------8.39
4----Part 2--------1-------------58.12
5----Part 2--------20-----------9.04
6----Part 2--------40-----------7.74
7----Part 3--------1------------60.00
8----Part 3--------20----------9.04
9----Part 3--------40----------7.74
And so on....

These are price breaks. I'd like to enter the Part number in cell G1
and the Qty in cell H1 and have the formula/array lookup the Price
Each. I searched the newsgroups and found the following article about
a "double lookup":

http://www.mvps.org/dmcritchie/excel/vlookup.htm

...and used the following formula:

=INDEX(C2:C10,MATCH(1,(A2:A10=G1)*(B2:B10=H1),0))

This works great if the quantity is an exact match. If I input Part 2
Qty 20, I get $9.04.

However, if I enter Part 2 Qty 21, I get an N/A since there isn't an
exact match

I thought I could change the match type of the array (the last 0) to
an approximate match, but I either get a N/A when I change it to -1 or
0, or the last value in column C if I change it to 1. Can I change
this array so I can get an approximate match so if I enter Part 2 Qty
21, I get $9.04 or Part 2 Qty 3 $58.12? Any help with this array or a
suggestion for another would be appreciated.

Thank You,

-Chad

 

[Peo Sjoblom]

One possible way (probably more and simpler ways)

=INDEX(INDEX(C2:C10,MATCH(G1,A2:A10,0)):INDEX(C2:C10,MATCH(G1,A2:A10)),MATCH(H1,INDEX(B2:B10,MATCH(G1,A2:A10,0)):INDEX(B2:B10,MATCH(G1,A2:A10))))


entered normally

as long as the layout is like the one you provided in your example

 

[Guest]

Here's another way, confirmed with CTRL+SHIFT+ENTER

=INDEX(C2:C10,MAX(IF(A2:A10=G1,IF(B2:B10<=H1,ROW(C2:C10)-ROW(C2)+1))))

 

[Dave Peterson]

Another way if you sort your table by column A in ascending order and then
column B in descending order.

The table would look like:

Part 1 80 8.39
Part 1 40 8.76
Part 1 2 23.01
Part 2 40 7.74
Part 2 20 9.04
Part 2 1 58.12
Part 3 40 7.74
Part 3 20 9.04
Part 3 1 60


And the array formula would look like:
=INDEX(C2:C10,MATCH(1,(A2:A10=G1)*(B2:B10<=H1),0))

 

[Robert McCurdy]

This seems to be what you are looking for Chad.
Array entered.

=INDEX(Vals,MATCH(LARGE(IF(Parts=pID,IF(Quant<=QID,Quant)),1),Quant,0))

Where the named ranges refer to your Parts, Quantity, and Values from your A,B,C columns respectively. pID and QID are your lookup values for Part no. and Quantity.

Use this one to look for higher or equal (>=) instead.

=INDEX(Vals,MATCH(SMALL(IF(Parts=pID,IF(Quant>=QID,Quant)),1),Quant,0))


Regards
Robert McCurdy
Hello,

I have a table that looks like this

-----A------------B-----------C-------
Part Number------Qty-------Price Each
1----Part 1---------2-------------23.01
2----Part 1--------40------------8.76
3----Part 1--------80-----------8.39
4----Part 2--------1-------------58.12
5----Part 2--------20-----------9.04
6----Part 2--------40-----------7.74
7----Part 3--------1------------60.00
8----Part 3--------20----------9.04
9----Part 3--------40----------7.74
And so on....

These are price breaks. I'd like to enter the Part number in cell G1
and the Qty in cell H1 and have the formula/array lookup the Price
Each. I searched the newsgroups and found the following article about
a "double lookup":

http://www.mvps.org/dmcritchie/excel/vlookup.htm

...and used the following formula:

=INDEX(C2:C10,MATCH(1,(A2:A10=G1)*(B2:B10=H1),0))

This works great if the quantity is an exact match. If I input Part 2
Qty 20, I get $9.04.

However, if I enter Part 2 Qty 21, I get an N/A since there isn't an
exact match

I thought I could change the match type of the array (the last 0) to
an approximate match, but I either get a N/A when I change it to -1 or
0, or the last value in column C if I change it to 1. Can I change
this array so I can get an approximate match so if I enter Part 2 Qty
21, I get $9.04 or Part 2 Qty 3 $58.12? Any help with this array or a
suggestion for another would be appreciated.

Thank You,

-Chad

 

[shaqattack1992-google]

Thanks to everyone form the help. I tried several of the solutions
and they did exactly what I needed.

Thanks!

-Chad