advanced lookup?

[Michel Khennafi]

Good morning to all:

I have a spreadsheet S1 where a cell B1 contains the value "MN" and a cell
A2 contains the value "AB"

I have a spreadsheet S2 where I have a cell A2 containing the value "MN" and
cell B2 contains "AB" and the cells C2 to L2 contain prices and the cells
above C1 to L1 contain the names of the suppliers.

I am trying to put in the cell B2 of the spreadsheet S1 a formula that would
retrieve the smallest price (obtained from the cells C2 to L2 in spreadsheet
S2) and the name of the supplier who has the smallest price (obtained from
the cells C1 to L1 in the spreadsheet S2). Ideal would be to eliminate the
zero or blank values from the formula

I tried to combine SMALL and INDEX with no success... hairs are getting
grayer by the minute here.

Can anyone assist?

Thanks a bunch for your time

MK

 

[ryguy7272]

I think this will give you what you want (or it should get you very close):
=INDEX(C1:L1,MATCH(MIN(C2:L2),C2:L2,0))
Modify to suit your needs.

Regards,
Ryan--