Using min function where more that one of the same MIN value is in row

[oblix]

I use a sheet to look up cheapest price and the match the company that has the cheapest price

Company Names in columns $L$7:$AE$7
below prices for each company (L10:AE10)

cheapest price in column j10 =MIN(L11:AE11)


in column i10 lookup company name for cheapest price =IF(J10=0," ",INDEX($L$7:$AE$7,MATCH(J10,L10:AE10,0)))

But how would I indicate too the user if two or more companies has the exact lowest price, and then show both companies in the cell for company with cheapest price?

 

[KRice]

Excel 2010 has limited capability for constructing the text string. Here is a somewhat clunky approach using functions available in Excel 2010. If you are willing to settle for a vertical list of the cheapest places, then use the formula shown in the helper column, and drag it down until a blank appears (indicating the last matching place with the cheapest price has been listed). The challenge in Excel 2010 is operating on that list to present all of the places in one text string in cell I10...you'll see that I used a manual concatenation (you could also do something like =CONCATENATE(H10,", ",H11,", ",H12) ), but neither approach adapts to the length of the list.

Book1
	H	I	J	K	L	M	N
7					Acme	Bob's	Sinclair
8
9	Helper	Cheapest Places	Cheapest
10	Acme	Acme, Sinclair,	4.5		4.5	4.65	4.5
11	Sinclair
12
13
Sheet1

Cell Formulas
Range	Formula
I10	I10	=H10&", "&H11&", "&H12
J10	J10	=MIN((L10:AE10))
H10:H12	H10	=IFERROR(INDEX($L$7:$AE$7,,AGGREGATE(15,6,COLUMN($L$10:$AE$10)-COLUMN($K$10)/($L$10:$AE$10=$J$10),ROWS(H$10:H10))),"")

 

[oblix]

Wow...thanks for all the effort
will try this in my worksheet
Thank you

 

[KRice]

I just noticed an error
=IFERROR(INDEX($L$7:$AE$7,,AGGREGATE(15,6,COLUMN($L$10:$AE$10)-COLUMN($K$10)/($L$10:$AE$10=$J$10),ROWS(H$10:H10))),"")
should be
=IFERROR(INDEX($L$7:$AE$7,,AGGREGATE(15,6,(COLUMN($L$10:$AE$10)-COLUMN($K$10))/($L$10:$AE$10=$J$10),ROWS(H$10:H10))),"")
where I wrapped the entire numerator that uses the COLUMN function with parentheses.

 

[oblix]

I have over 900 products directly under each other.
dont know how to make this work for each row (900)/product as this method needs more than one row per product.
any suggestions?

 

[KRice]

Yes, if you want to list the cheapest places across columns in the same row, you could turn the list like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
7												Acme	Bob's	Sinclair	Elway	Starling's
8
9	Helper Columns for Cheapest Places									Cheapest
10	Acme	Sinclair	Elway							4.5		4.5	4.65	4.5	4.5	4.65
11	Bob's	Elway								4.48		4.63	4.48	4.51	4.48	4.8
12
Sheet1

Cell Formulas
Range	Formula
A10:E11	A10	=IFERROR(INDEX($L$7:$AE$7,,AGGREGATE(15,6,(COLUMN($L10:$AE10)-COLUMN($K10))/($L10:$AE10=$J10),COLUMNS($A10:A10))),"")
J10:J11	J10	=MIN((L10:AE10))

 

[oblix]

Thank you that will work

 

[KRice]

Glad to help