Return highest value

[Peter1973]

I am looking to look in A1:Z1 for any cells that = A5 and then for those cells return the highest value from A2:Z2.

any help appreciated.

 

[FormR]

Hi, you could try:

=MAXIFS(A2:Z2,A1:Z1,A5)

 

[Joe4]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have Excel 2019 or newer, you can use the new MAXIFS formula like FormR showed.
Here is a link to details on that: MAXIFS function - Microsoft Support

 

[Peter1973]

Hi, you could try:

=MAXIFS(A2:Z2,A1:Z1,A5)

Thanks this seems to not pull the highest value but the last one ie the data from Z2

 

[FormR]

Hi, here's a demo, if you are getting different results it would be helpful if you posted some sample data.

Book2
	A	B	C	D	E	F	G
1	a	a		b			a
2		99		999			1
3
4
5	a
6	99
Sheet1

Cell Formulas
Range	Formula
A6	A6	=MAXIFS(A2:Z2,A1:Z1,A5)

 

[Joe4]

Thanks this seems to not pull the highest value but the last one ie the data from Z2

Are you sure that the values in row 2 are valid numbers, and not "numbers entered as text"?

 

[Peter1973]

Ignore me i had not changed the format of A5, thanks for the help