# Compare values and output cell titles, able to output multiple results

1. ## Compare values and output cell titles, able to output multiple results

Hi everybody!

I'm new to this forum so hello to everyone around the world, hope you are having a great weekend.

I liked to think until today that I am self-sufficient on Excel but alas, I am definitely not...

I have a spreadsheet where I am comparing weather conditions in multiple cities. I want the 'winner' to be outputted in a cell but if there is a tie I would like all of the equal results to be displayed.

Here's a quick example of what I want...

 Highest Temperature London Temperature Paris Temperature Madrid Temperature Paris, Madrid 21 24 24

At present I have a formula like so for the 'highest temperature' cells, it doesn't seem to work: =INDEX(\$E\$2:\$AF\$2,MATCH(MIN(H28,L28,P28,T28,X28,AB28,AF28),H28,L28,P28,T28,X28,AB28,AF28,0)). I get the error 'you've entered too many arguments for this function'. Any ideas why? The first group is the cell titles e.g. London, Paris, Madrid, going across the top row.

I realise this won't yet output multiple values if there is a tie also, so if anyone has any ideas on that I'm all ears! Please note I can't use ':' for the second and third group selection because there are cells in-between being used for other things.

Thanks to anyone who might be able to help me

Mark

2. ## Re: Compare values and output cell titles, able to output multiple results

This looks like a nightmare!

You ask for the highest temps but your formula is using the MIN (lowest temp) function.

It looks like you want the duplicate temp results to be in a single cell? If so, that will require a VBA function.

Your formula indexes the range E2:AF2 but your MIN function starts at column H.

This should be doable but it will be very complicated.

3. ## Re: Compare values and output cell titles, able to output multiple results

Hi Tony,

Thank you for the reply! It looks like I copied the wrong piece of code for you, the one I am trying to use is indeed using MAX. Sorry about that. Here's the correct piece, which excel returns the 'too many arguments' error for.

=INDEX(\$E\$2:\$AF\$2,MATCH(MAX(G5,K5,O5,S5,W5,AA5,AE5),G5,K5,O5,S5,W5,AA5,AE5,0))

First figures are the city titles, second and third groups are the cells which contain the numerical temperature for the respective cities.

Mark

4. ## Re: Compare values and output cell titles, able to output multiple results

Are you OK with using a VBA function?

5. ## Re: Compare values and output cell titles, able to output multiple results

I haven't yet attempted the duplicate functionality. I have never used a VBA function before, is it straightforward or a bit of a nightmare?

Is there another, simpler way of achieving a similar result for outputting multiple identical values?

6. ## Re: Compare values and output cell titles, able to output multiple results

Originally Posted by TMG2016
I haven't yet attempted the duplicate functionality. I have never used a VBA function before, is it straightforward or a bit of a nightmare?
At first, you'll think it's a nightmare but once you get it setup you won't even notice it.

Is there another, simpler way of achieving a similar result for outputting multiple identical values?
Yes!

Put duplicates (if any) in separate cells. That way no VBA required.

7. ## Re: Compare values and output cell titles, able to output multiple results

Ok, I can do that, thanks Tony .

Do you have any idea as to where I might be going wrong with my formula?

8. ## Re: Compare values and output cell titles, able to output multiple results

Originally Posted by TMG2016

Do you have any idea as to where I might be going wrong with my formula?
You need to add a test for the column numbers.

Give me a few minutes...

9. ## Re: Compare values and output cell titles, able to output multiple results

Try this for the lowest temp. For the highest temp replace all references to MIN with MAX.

Data Range
 G H I J K L M N O 5 City1 City2 City3 6 50 62 50 7 ------ ------ ------ ------ ------ ------ ------ ------ ------ 8 9 City1 10 City3 11

This array formula** entered in G9:

=IFERROR(INDEX(G\$5:O\$5,SMALL(IF(MOD(COLUMN(G\$5:O\$5),4)=3,IF(G\$6:O\$6=MIN(G\$6:O\$6),COLUMN(G\$6:O\$6)-COLUMN(G\$6)+1)),ROWS(G\$9:G9))),"")

** 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.

Copy down until you get blanks.

10. ## Re: Compare values and output cell titles, able to output multiple results

Thanks so much for taking the time to do this Tony! I haven't got it working just yet but I think I might be able to tweak a few things on my sheet to get it working.

The main issue I think I have is that there is other data in the cells between the temperature cells, so I can't select a range (i.e. I have to enter "cell, cell, cell" instead of "cell:cell"). Any way around that or shall I just re-work the sheet so I can use arrays?

If it's not too much to ask, would you mind explaining to me what the static numbers represent in that formula such as the 4, 3 and +1?

Appreciate all the time you've given!

11. ## Re: Compare values and output cell titles, able to output multiple results

Originally Posted by TMG2016

The main issue I think I have is that there is other data in the cells between the temperature cells, so I can't select a range (i.e. I have to enter "cell, cell, cell" instead of "cell:cell").
Yes you can!

If there are numbers in the between cells then use this version...

Data Range
 G H I J K L M N O 5 City1 header header header City2 header header header City3 6 15 15 0 0 23 1 5 15 15 7 8 9 City1 10 City3

=IFERROR(INDEX(G\$5:O\$5,SMALL(IF(MOD(COLUMN(G\$5:O\$5),4)=3,IF(G\$6:O\$6=MIN(IF(MOD(COLUMN(G\$5:O\$5),4)=3,G\$6:O\$6)),COLUMN(G\$6:O\$6)-COLUMN(G\$6)+1)),ROWS(G\$9:G9))),"")

Still array entered.

If it's not too much to ask, would you mind explaining to me what the static numbers represent in that formula such as the 4, 3 and +1?
The 4 and 3 are used to identify the columns where the data of interest is located.

We look at the column number and divide it by a number that returns a common value that we use as a pattern that we search for.

For example:

G5 = column number 7
K5 = column number 11
O5 = column number 15

If we divide those column numbers by 4 we get a remainder of 3:

MOD(7,4) = 3
MOD(11,4) = 3
MOD(15,4) = 3

So, we look for columns that return 3 when their column number is divided by 4.

The +1 is used as an "offset correction".

The cell references in the INDEX function are RELATIVE to the range.

INDEX(X22:X32...)

Cell X22 is in position 1 relative to the range X22:X32.

Cell X25 is in position 4 relative to the range X22:X32.

In the formula we have to convert the ABSOLUTE column references to RELATIVE references to be compatible with the INDEX function.

COLUMN(G\$6:O\$6)-COLUMN(G\$6)+1

COLUMN(G\$6)-COLUMN(G\$6)+1 = 1
COLUMN(H\$6)-COLUMN(G\$6)+1 = 2
COLUMN(I\$6)-COLUMN(G\$6)+1 = 3
COLUMN(J\$6)-COLUMN(G\$6)+1 = 4
etc
etc

There are currently 1 users browsing this thread. (0 members and 1 guests)