Return column title based on smallest value by row?

**realrookie** · 09-29-2015, 08:41 AM

Hello,

C2 to G2 have values in them. Is there a way to determine the lowest value ( other then SMALL function) and then return values to the according 1:1 row?

Example:

Name Test1 Test2 Test3 Test4 Test5 Weak Expected value
CAR1 66.7% 40.0% 100.0% 71.4% 50.0% =formula Test2
CAR2 100.0% 20.0% 40.0% 57.1% 16.7% Test5
CAR3 33.3% 0.0% 40.0% 57.1% 0.0% Test2,Test5

sample2.xlsx

Regards,

RR

**Jonmo1** · 09-29-2015, 08:59 AM

Here's a start..

In M2 and filled down
=INDEX(C$1:G$1,MATCH(MIN(C2:G2),C2:G2,0))

Now this will only return the first instance of the lowest value.
Getting the multiple results like you show in M4 will be much much more complicated.
Will probably require VBA.

**realrookie** · 09-29-2015, 10:18 AM

Thank you for the reply Jonmo1,

It will get me started at least. In the real data we have several times where we would get 3 or even 5 results back, so it would be handy but I can work with this

**Tony Valko** · 09-29-2015, 10:27 AM

Originally Posted by realrookie

In the real data we have several times where we would get 3 or even 5 results back, so it would be handy

Are you familiar with macros/VBA ?

This can be done with a VBA function. Want to try it?

**Jonmo1** · 09-29-2015, 10:21 AM

You're welcome.

**realrookie** · 09-29-2015, 10:43 AM

I am somewhat familiar with VBA. Everything I learn has come from here. Always happy to learn more

**Tony Valko** · 09-29-2015, 10:58 AM

With your file open, copy the VBA code at this link and paste it into a general module.

https://www.excelforum.com/showthread.php?p=3096647

Then...

Data Range

	A	B	C	D	E	F	G	H	I
1	Name		Test1	Test2	Test3	Test4	Test5		Expected value
2	CAR1		66.7%	40.0%	100.0%	71.4%	50.0%		Test2
3	CAR2		100.0%	20.0%	40.0%	57.1%	16.7%		Test5
4	CAR3		33.3%	0.0%	40.0%	57.1%	0.0%		Test2, Test5
5	------	------	------	------	------	------	------	------	------

This array formula** entered in I2 and copied down:

=concatall(IF(C2:G2=MIN(C2:G2),C$1:G$1,""),", ")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**realrookie** · 09-29-2015, 11:33 AM

Hello Tony,

How exactly do I enter the array formula?
Type =concatall(IF(C2:G2=MIN(C2:G2),C$1:G$1,""),", ") into the cell and hit CRTL+SHIFT and ENTER? Because when I do that it enters that formula as text?

**Tony Valko** · 09-29-2015, 12:58 PM

That sounds like the cell is formatted as text.

Change the cell format to General then re-enter the formula.

Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you must use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you must use the key combo to produce them. Also, anytime you edit an array formula it must be re-entered as an array using the key combo.

Here's your file with this implemented.