help with lookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Craig McLaughlin - 31 Dec 2007 17:29 GMT

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

Reply to this Message

Bob Phillips - 31 Dec 2007 17:38 GMT

Cheapest price

=MIN(B2:M2)

Supplier

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

Signature

---
HTH

Bob

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

>I have a large table with a list of items and their different prices from
>different suppliers.
[quoted text clipped - 19 lines]
>
> craig

Reply to this Message

Craig McLaughlin - 31 Dec 2007 17:55 GMT

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(B1:D1,MATCH(MIN(B2:D2),B2:D2,0)

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

Thanks

craig

> Cheapest price
>
[quoted text clipped - 27 lines]
>>
>> craig

Reply to this Message

Craig McLaughlin - 31 Dec 2007 17:57 GMT

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

thanks

Craig

> Cheapest price
>
[quoted text clipped - 27 lines]
>>
>> craig

Reply to this Message

Rate this thread: