vlookup 2 fields

[cturner]

One thing that I forgot to mention in my earlier Post is that once it
does a match on the part#, it needs to lookup the qty and bring back
the price that is closest to the qty. Using the example from before.
If I have a Part# of 1, qty of 7 (there is not listed qty of 7) then
it needs to give me the price of 50 (which would be the 5 qty). That
is how the vlookup works and that is what I really needed.
Thanks again.. sorry I forgot that "minor" detail
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

 

[Pete_UK]

In your example both sets of prices are the same and both are 10 times
the quantity - I assume that in real life this is not the case and
that you might offer a discounted price the larger the quantity.

I would suggest thatyou re-organise your data like this:

| qty | 2 | 5 | 10 | 20 | 30 | 50
Part# |
1 | | 20 | 50 | 100 | 200 | 300 | 500
2 | | 20 | 50 | 100 | 200 | 300 | 500
5 | | 20 | 45 | 90 | 175 | 270 | 440
7 | | 10 | 24 | 47 | 92 | 135 | 225
9 | | 16 | 40 | 78 | 155 | 225 | 375
10 | | 20 | 48 | 95 | 188 | 275 | 450

I have arranged the quantities across the sheet in C1 to H1, and the
part numbers to go down the sheet from A3 to A8. I have "invented"
some more data so that I can test this out. In my mock up I used A11
for the part number and B11 for the quantity, and put this formula in
C11:

=IF(ISNA(MATCH(A11,A3:A8,0)),"not
present",INDEX(C3:H8,MATCH(A11,A3:A8,0),MATCH(B11,C1:H1,1)))

(All one formula - I've split it to avoid awkward line breaks).

I realise you would have to re-arrange your data, but I hope this
helps.

Pete

 

[Guest]

Select a whole data range A1:C13 > Data > Sort > Sort by select qty >
Descending order > Then by "leave it blank" > Then by "leave it blank" > My
list has select Header row > click OK

=INDEX(C2:C13,MATCH(1,(A2:A13=1)*(B2:B13<=7),0))

ctrl+shift+enter, not just enter

 

[vezerid]

One thing that I forgot to mention in my earlier Post is that once it
does a match on the part#, it needs to lookup the qty and bring back
the price that is closest to the qty. Using the example from before.
If I have a Part# of 1, qty of 7 (there is not listed qty of 7) then
it needs to give me the price of 50 (which would be the 5 qty). That
is how the vlookup works and that is what I really needed.
Thanks again.. sorry I forgot that "minor" detail
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

For the amended requirements you can use this formula (Part# in E1,
Qty in F1).

=INDEX($C$1:$C$9,MATCH(F1,IF($A$1:$A$9=E1,$B$1:$B$9),1))

This is an *array* formula, thus you have to commit with Shift+Ctrl
+Enter.

HTH
Kostis