help with lookup

[Craig McLaughlin]

I have a large table with a list of items and their different prices from
different suppliers.

I am looking for a function to look at each item in a row, define the
cheapest supplier, the column and then return the supplier name in another
sheet. I have tried Vlookup and Hlookup but I can get it to work can anyone
help.

Item Supplier 1 Supplier 2 Supplier 3

Crisps 10 15 11
Juice 30 20 19
Beer 15 25 27

So at the end of each row I would have a cell showing cheapest price and who
supplies it. The items and numbers can't be in order as there are two
many products and they are in categories.

Can anyone help please

thanks

craig

 

[Bob Phillips]

Cheapest price

=MIN(B2:M2)

Supplier

=INDEX(B$1:M$1,MATCH(MIN(B2:M2),B2:M2,0))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Craig McLaughlin]

Hi thanks for the fast reply.

I am not an expert in excel but I seem to have trouble with this as it does
not disply any data

the min price bit is easy it is returning the supplier name that seems to be
the sticking point

Using the table below I would key
I have =INDEX(B11,MATCH(MIN(B22),B22,0)

but nothing appears - what is the zero for at the end?

Thanks

craig

 

[Craig McLaughlin]

I got it I had selected the wrong array option for the index

thanks

Craig