Rate cards - return winning supplier with minimum value

[MatthewPaul]

Hi there, I produce a lot of rate cards with multiple suppliers and would like some help with a return function.

Currently, the formula I'm using is =INDEX($V$1:$AA$1,MATCH(MIN(V2:AA2),V2:AA2,0))

**Please see screenshot**

There are two things I'm looking for support with.
1. If two suppliers have the same bid, how do I return both suppliers in 'winning supplier' cell
2. If there is a 0.00 bid in one of the cells, how do I exclude that bid from the 'winning supplier' formula. I need this as I might have bids in other currencies and in the range of this formula, I might have = x * currency exchange. Until I have a bid in there, it comes up with a 0.00 value.

Thank you very much and I appreciate your support in advanced.

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if this would work for you.

23 11 06.xlsm
	V	W	X	Y	Z	AA	AB
1	Supplier 1	Supplier 2	Supplier 3	Supplier 4	Supplier 5	Supplier 6	Winner
2	1	1	6	6	1	3	Supplier 1, Supplier 2, Supplier 5
3	3	6	0	0	1	3	Supplier 5
4	5	9	8	2	0	7	Supplier 4
5	4	5	6	9	4	4	Supplier 1, Supplier 5, Supplier 6
6	0	0	0	0	0	0
7	9	2	3	5	2	5	Supplier 2, Supplier 5
Suppliers

Cell Formulas
Range	Formula
AB2:AB7	AB2	=BYROW(V2:AA7,LAMBDA(rw,TEXTJOIN(", ",,IFERROR(FILTER(V1:AA1,rw=SMALL(rw,1+COUNTIF(rw,0))),""))))
Dynamic array formulas.

 

[MatthewPaul]

Welcome to the MrExcel board!

For the future I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if this would work for you.

23 11 06.xlsm
	V	W	X	Y	Z	AA	AB
1	Supplier 1	Supplier 2	Supplier 3	Supplier 4	Supplier 5	Supplier 6	Winner
2	1	1	6	6	1	3	Supplier 1, Supplier 2, Supplier 5
3	3	6	0	0	1	3	Supplier 5
4	5	9	8	2	0	7	Supplier 4
5	4	5	6	9	4	4	Supplier 1, Supplier 5, Supplier 6
6	0	0	0	0	0	0
7	9	2	3	5	2	5	Supplier 2, Supplier 5
Suppliers

Cell Formulas
Range	Formula
AB2:AB7	AB2	=BYROW(V2:AA7,LAMBDA(rw,TEXTJOIN(", ",,IFERROR(FILTER(V1:AA1,rw=SMALL(rw,1+COUNTIF(rw,0))),""))))
Dynamic array formulas.

Hi Peter, this is absolutely brilliant. Thank you so much.
I really appreciate it. I hope you have a lovely day.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.