Multiple lookup values

[Pierre]

We would like to have vlookup extend its search (looking for cost) to
include a value indicating a cost/price-break for any particular part.
If the item is not found in the parts history worksheet, should not go
on to the next line for pricing, but return a blank, or #N/A

Active Sheet
Part Number Quantity Cost PO Date
A124 10
A124 50
B50 6


Parts Purchasing History
Part Number Qty Cost PO Date
A124 5 5.25 11/20/2002
A124 25 4.30 9/6/2003
B55 6 12.00 12/6/2002

The desired result would be:

Active Sheet
Part Number Quantity Cost PO Date
A124 10 5.25 11/20/2002
A124 50 4.30 9/6/2003
B50 6 #N/A #N/A

(#N/A returned could also be a be a blank cell)

This ones a real head scratcher. TIA for any thoughts.

PC

 

[Harlan Grove]

We would like to have vlookup extend its search (looking for cost) to
include a value indicating a cost/price-break for any particular part.
If the item is not found in the parts history worksheet, should not go
on to the next line for pricing, but return a blank, or #N/A

Active Sheet
Part Number Quantity Cost PO Date
A124 10
A124 50
B50 6

Parts Purchasing History
Part Number Qty Cost PO Date
A124 5 5.25 11/20/2002
A124 25 4.30 9/6/2003
B55 6 12.00 12/6/2002

More details needed. There are two separate entries for part number A124 in both
tables. The entries in the second table contain additional details, but the
Quantity field entries in both tables differ. Do you mean that the first entry
for part number A124 in the active sheet should match the first entry for the
same part number in the second table, and the second entry in the active sheet
match the second entry in the second table, etc.?

 

[Pierre]

-----Original Message-----
...

More details needed. There are two separate entries for part number A124 in both
tables. The entries in the second table contain additional details, but the
Quantity field entries in both tables differ. Do you mean that the first entry
for part number A124 in the active sheet should match the first entry for the
same part number in the second table, and the second entry in the active sheet
match the second entry in the second table, etc.?

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.
.

Yes. You described it exactly. (Want to pick up the
closest price to historical price data, without going
over.)
Example 1:
Wanted to get 10 piece price. History contains 5, and 25
piece buys.) 10 would not qualify for 25 piece pricing,
so,5 piece price is returned.
Example 2:
Wanted to get 50 piece price. History contains 5, and 25
piece buys.) 25 piece price is returned, rather than
moving to next item(B55) and obtain incorrect price data.
Example 3:
There is no purchasing data for item B50 at any quantity.
Blank, or #N/A is returned

Thanks for all.
PC

 

[Harlan Grove]

[[email protected] wrote...]

"Pierre" wrote...

I'll assume this is in A14 with Part Number in cell A1.

I'll assume the entire table is named PPH and that it's sorted first in
ascending order by part number then in ascending order by quantity.

Yes. You described it exactly. (Want to pick up the
closest price to historical price data, without going
over.)

...

Try the following formulas.

C2:
=VLOOKUP($B2,OFFSET(PPH,MATCH($A2,INDEX(PPH,0,1),0)-1,1,
COUNTIF(INDEX(PPH,0,1),$A2),2),2)

D2:
=VLOOKUP($B2,OFFSET(PPH,MATCH($A2,INDEX(PPH,0,1),0)-1,1,
COUNTIF(INDEX(PPH,0,1),$A2),3),3)

Select C22 and fill down as far as needed.

 

[Pierre]

-----Original Message-----

[[email protected] wrote...]

...
We would like to have vlookup extend its search (looking for cost) to
include a value indicating a cost/price-break for any particular part.
If the item is not found in the parts history worksheet, should not go
on to the next line for pricing, but return a blank, or #N/A

Active Sheet
Part Number Quantity Cost PO Date
A124 10
A124 50
B50 6

I'll assume this is in A14 with Part Number in cell A1.

I'll assume the entire table is named PPH and that it's sorted first in
ascending order by part number then in ascending order by quantity.

Yes. You described it exactly. (Want to pick up the
closest price to historical price data, without going
over.)

...

Try the following formulas.

C2:
=VLOOKUP($B2,OFFSET(PPH,MATCH($A2,INDEX(PPH,0,1),0)-1,1,
COUNTIF(INDEX(PPH,0,1),$A2),2),2)

D2:
=VLOOKUP($B2,OFFSET(PPH,MATCH($A2,INDEX(PPH,0,1),0)-1,1,
COUNTIF(INDEX(PPH,0,1),$A2),3),3)

Select C22 and fill down as far as needed.

--

Snip<<<

.

Thank you! Thank you!! This worked like a charm, and
you, are a genius. Thanks again.
PC