Comparison of Multiple numeric colums with result in text

I want to do the comparison of 5 companies for multiple destinations and find out the cheapest rate among them BUT in the last column I want the company name instead of the cheapest rate. (I've attached the file so its easy to understand.)


Can someone please tell me the formula for that?



Thanks & Regards

Suppose Company names are in C1:G1 and prices are in C2:G2, then

Please Login or Register  to view this content.

This finds the location of the lowest value using MIN in MATCH, then uses INDEX to pick the corresponding value out of the co names.

Originally Posted by outofthehat

Suppose Company names are in C1:G1 and prices are in C2:G2, then

Please Login or Register  to view this content.

This finds the location of the lowest value using MIN in MATCH, then uses INDEX to pick the corresponding value out of the co names.

Thanks. It works with 2 colums, but i need it to work with 5. I've converted it to the following

=INDEX(D4:E4:F4:G4,H4,1,MATCH(MIN(D6:E6:F6:G6:H6),D6:E6:F6:G6:H6,0))

but its not giving any results.


I've attached the excel sheet, please guide me as to what i;m doing wrong.


thanks.

Originally Posted by inayat

Please Login or Register  to view this content.

You were just giving the wrong range... the range has to be the entire range from starting point to end point

lolz...oke... thanks. it works now

one small question, as i'm comparing like 40 lines, when i copy paste the formule, its not working. what to do?

Original formula.
=INDEX(D4:H4,1,MATCH(MIN(D33:H33),D33:H33,0))


Changed formula
=INDEX(D5:H5,1,MATCH(MIN(D34:H34),D34:H34,0))


where the range D4:H4, should remain fixed. Please guide.

Please Login or Register  to view this content.

We just needed to freeze the Company Names data by using the dollar sign...

One small query, what if the two colums we are comparing have the same value? I'm comparing 5 columns and two of them have the same value BUT the name which is coming is the first one (the one which is in the first column).


PLEASE HELP.

Please can someone help

Please....someone help...

Hope the attached helps. I've added conditional formatting to alert duplicate minimum rates.

Originally Posted by Sofistikat

Hope the attached helps. I've added conditional formatting to alert duplicate minimum rates.

Thanks but its not working. In the attachment as well, company 2 & 5 have the same rates but the result shows Company 2 as teh cheapes.

Originally Posted by Richard Buttrey

Yes,

I understand which are the duplicates but what do you want to do about them? If you want to highlight them the following conditional format in D6 and applied to D6:H6 would do that.

Please Login or Register  to view this content.

Regards

what i want to do is that if 2 companies have the same rate, the result can show both of their names. Is it possible?