Re: Double Lookup Without Exact Match-Price Breaks

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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
<shaqattack1992-google@xxxxxxxxx> wrote in message news:1184623842.292526.317920@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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

.

Relevant Pages