Lookup Question

[Craig92604]

Have the follow question:

Product Code Vendor 1 Vendor 2 Vendor 3 Vendor 4
ABCG $1.00 2.50 1.18 .99


I would like to return the venor name who selling the Product for the cheapest price. I know this is possible but since a changed jobs 6 months ago I don't get much of an opportunity to use Excel...I'm a bit rusty

Thank you!

 

[Barrie Davidson]

=INDEX($B$1:$D$1,,MATCH(MIN(OFFSET($B$1:$D$1,MATCH($G$1,$A$2:$A$4,0),0)),OFFSET($B$1:$D$1,MATCH($G$1,$A$2:$A$4,0),0),0))

Assumptions:
-Your data table is in $A$1:$D$4
-The vendor numbers are in $B$1:$D$1
-The part numbers are in $A$2:$A$4
-The part number being looked up is in $G$1

Just change the ranges accordingly.

Regards,

 

[DonkeyOte]

assuming names in row 1 and range therefore (including vendor names) is A1:F2 then your formula could be

=index(A1:F1,1,match(min(B2:F2),A2:F2,0))

 

[Aladin Akyurek]

See my contrib in:

http://www.mrexcel.com/board2/viewtopic.php?t=72823&start=0