Compare 3 columns and show header

[Reshaw]

Hi there

I'm struggling to find a nice solution for the following problem

I maintain a spreadsheet that compares our prices against our competition, so i have 3 columns with prices in - each row corresponding to a different postcode destination.

What i need to do is on the 4th column show the text of who is the cheapest. The text is the top of each column

I want the output something like the following

Postcode Supplier1 supplier2 supplier3 cheapest
AB 10 12 14 supplier1
AL 20 18 16 supplier3
B 35 25 30 supplier2

I've played around with MIN() function and tried to match the result to the column but can't figure out how to match a column to a heading.

thanks in advance for any suggestions

 

[VoG]

Try in E2 copied down

=INDEX(B$1:D$1,MATCH(MIN(B2:D2),B2:D2,0))

 

[Roj47]

I am VERY messy when writing formulae, but they work.

No doubt a better solution will be along, but in one column (hidden) have a nested IF function.

In column G add : =MIN(D9:F9)
In Column H add : =IF(G9=D9,D8,IF(G9=E9,E8, IF(G9=F9,F8,"")))

 

[Reshaw]

Ok thanks everyone

I pasted Vogs answer in first (purely because it was the first reply) and it seems to work

what i didn't tell you is that the three columns I'm comparing are not adjacent

in detail the structure is as follows

Supplier1 supplier2 Supplier1 Cheapest
Postcode 1 pallet 2 pallets 3 pallets 4 pallets 5 pallets 1 pallet 2 pallets 3 pallets 4 pallets 5 pallets 1 pallet 2 pallets 3 pallets 4 pallets 5 pallets 1 2 3 4 5
AB
AL
B
ETC



Not sure if that will display correctly or not

but essentially for supplier 1 there are 5 columns denoting consignment size and the same for 2 other suppliers, I need to work out who is the cheapest for each consignemnt size

I realise that I could put all the 1 pallet columns together and the 2 pallets etc, but we update this as and when new tariffs come to us and we would need all the prices for each supplier together ideally.

Is there a better way of showing the tables to you?

 

[VoG]

You can use Excel Jeanie to post a shot of your sheet

http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

The formula that I posted will not work with non-adjacent columns.