Double Lookup Without Exact Match-Price Breaks
 

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 would be greatly appreciated.

Thank You,

-Chad

Double Lookup Without Exact Match-Price Breaks
 

You have a response at your other post.

wrote:

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 would be greatly appreciated.

Thank You,

-Chad

--

Dave Peterson