Comparing 3+ columns and need to return clumn header of lowest (or column headers)

[giantwolf]

Hi,

I have a table of data with 3 columns of data I want to compare (could be more columns though) and I want to return the header of the column that has the lowest value. If two or more columns are of equal size (and are the joint lowest) then I'd like the column headers returned together.

I've managed to create a formula to return the column header (c2,d2 or e2) with the lowest value:- =CHOOSE(MATCH(SMALL(C4:E4,1),C4:E4,0),$C$2,$D$2,$E$2), however I haven't been table to take it that one step further whereby it would return C2 & D2 in the same cell IF both matched and were the smallest.

EXCEL SCREENSHOT.JPG

Is it possible to do this?

[Glenn Kennedy]

the cleanest way to do this is with a UDF:

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


Then use this array formula:

=ConcatAll(IF(D2:F2=MIN(D2:F2),$D$1:$F$1,""),", ")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[giantwolf]

Glenn,

Really appreciate the response - is there anyway to do this without the VBA?

Thanks,

Richard.

[Glenn Kennedy]

Define the maximum number of columns that could be involved....

[giantwolf]

If I say for now "3" - I can then adjust as required.

[Glenn Kennedy]

Up to a point.... If there are many ore than 3... any other solution will get very messy. I will provide a solution for 3, but I would like an answer to my Q...

[giantwolf]

Sorry, not trying to be evasive - really appreciate the help. I can't see it being above 5 or 6.

Essentially its a quote comparison of suppliers - normally we'd get a price from a current supplier and then 2 others (hence the 3), but it could be more

Is 5 or 6 too many?

[giantwolf]

really appreciate the quick responses by the way

[Glenn Kennedy]

Ok. Thats do-able. Look up in a short time...

[giantwolf]

thank you, will do.

[Glenn Kennedy]

Option 1 was VBA.

Option 2 delivers results in separate cells (again an array formula, copied across and down):
=IFERROR(INDEX($F$1:$H$1,SMALL(IF($F2:$H2=MIN($F2:$H2),COLUMN($F2:$H2)-COLUMN($F2)+1),COLUMNS($I:I))),"")

Option 3 delivers results in a single celll (again, an array formula copied down: set for up to 3 minimum values):

Please Login or Register  to view this content.

see sheet for context.

Obviously, if you go for one of the formula versions, save as .xlsx,rather than .xlsm

[giantwolf]

Brillliant - thank you!

All 3 solutions very useful.

Really appreciate your efforts with this, i'd never have got there on my own.

[Glenn Kennedy]

You're welcome and thanks for the rep.