Lookup formula

[Ruan]

Hello

Is it possible to find the minimum Price for each Part and then display the
Company that sells this part?


A B C D
E F

Part Company A Company B Company C Min Price
Company
Part 1 $23.99 $21.98 $27.55
Part 2 $87.42 $95.34 $72.63
Part 2 $53.76 $62.51 $61.87


Thanks
Ruan

 

[Biff]

Hi!

To find the minimum (lowest) price:

=MIN(B22)

To find the company:

=INDEX(B11,1,MATCH(MIN(B22),B22,0))

Note: if there is more than one instance of the lowest
price both formulas will return the *first* instance found.

For ex: Company1 100 and Company3 100.

Biff

 

[Ruan]

Thanks Biff. That worked great.

Hi!

To find the minimum (lowest) price:

=MIN(B22)

To find the company:

=INDEX(B11,1,MATCH(MIN(B22),B22,0))

Note: if there is more than one instance of the lowest
price both formulas will return the *first* instance found.

For ex: Company1 100 and Company3 100.

Biff

 

[Max]

Part 1 $23.99 $21.98 $27.55

Part 2 $87.42 $95.34 $72.63
Part 2 $53.76 $62.51 $61.87

You had *2* "Part 2"'s in your original descript
So this was probably a typo then?

But I'm not sure it was read as that though <g>,
If it wasn't a typo, then the problem would have assumed
a new dimension ..
(I didn't have any suggestions to offer, but I was looking
to learn from insights of others who might have read it that way
and their suggestions ..)

 

[Biff]

Hi Max!

If there were two rows of entries for Part 2 you would
need a separate formula for that. Something like this will
do it: array entered

=INDEX(B11,MAX((MIN(B23)=B23)*COLUMN(B23))-CELL
("Col",$B$1)+1)

Biff

 

[Max]

Thanks, Biff !

Tinkered around with some ideas from your suggestion

If the OP's descript was taken that
there might be repeated Part#s in col A ..

With the table below assumed in Sheet1,
cols A to D, data from row2 down:

Part # CompA CompB CompC
Part 1 $23.99 $21.98 $27.55
Part 2 $87.42 $95.34 $72.63
Part 2 $53.76 $62.51 $61.87

etc

And perhaps in another Sheet2,
if the OP had a table below with the
Part#s listed down in col A, A2 down:

Part # MinPrice Company
Part 1
Part 2
Part 3
etc

then ..

Array-entered in B2:
=MIN(IF(Sheet1!$A$2:$A$100=A2,Sheet1!$B$2:$D$100))

Array-entered in C2:
=INDEX(Sheet1!$B$1:$D$1,MAX((Sheet1!$A$2:$A$100=A2)*(Sheet1!$B$2:$D$100=B2)*
COLUMN($A$1:$C$1)))

B2:C2 selected and then copied down
would return the minimum prices for the Part# in col B
and the company in col C

The caveat on tied prices (if any) for any Part# would remain:
the company returned in col C would be the "last instance"
i.e. one in the highest row# for the particular tie

(Ranges in formula are arbitrary and to be adapted to suit, of course)
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

Hi Max!

If there were two rows of entries for Part 2 you would
need a separate formula for that. Something like this will
do it: array entered

=INDEX(B11,MAX((MIN(B23)=B23)*COLUMN(B23))-CELL
("Col",$B$1)+1)

Biff