Double Lookup Without Exact Match-Price Breaks

  • Thread starter shaqattack1992-google
  • Start date
S

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
 
P

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
 
D

daddylonglegs

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))))
 
D

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))
 
R

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
 
S

shaqattack1992-google

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

Thanks!

-Chad
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top