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.

What about the duplicates?

Are you OK with using a VBA function?

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.

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

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.

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