MATCH Formula
 

Hello Group
I need formula help....
I need to find the Max value of A1:Z1 and then match it with the value in
the row where "CAT" resides in Col A in the range A2:Z23. The target
value("CAT") may be in one row one time and another the next.
Thanks!

Would you provide a small sample along with the expected result?

Sandy wrote:
Hello Group
I need formula help....
I need to find the Max value of A1:Z1 and then match it with the value in
the row where "CAT" resides in Col A in the range A2:Z23. The target
value("CAT") may be in one row one time and another the next.
Thanks!

Sorry one slight typo Max of b1:z1 and match A2:Z23
An abbreviated example...
10 15 18 20 5 0
Dog 100 28 53 26 18 12
Cat 50 18 26 47 7 16
Fish 55 15 23 58 72 8
Turtle 5 10 15 20 25 30

Find max in Row 1= 20
Find matching value in the row where Cat is in Col1 = 47
The row that Cat is in may vary.

HTH



"Aladin Akyurek" wrote:

Would you provide a small sample along with the expected result?

Sandy wrote:
Hello Group
I need formula help....
I need to find the Max value of A1:Z1 and then match it with the value in
the row where "CAT" resides in Col A in the range A2:Z23. The target
value("CAT") may be in one row one time and another the next.
Thanks!

Sandy wrote...
Sorry one slight typo Max of b1:z1 and match A2:Z23
An abbreviated example...

10 15 18 20 5 0
Dog 100 28 53 26 18 12
Cat 50 18 26 47 7 16
Fish 55 15 23 58 72 8
Turtle 5 10 15 20 25 30

Find max in Row 1= 20
Find matching value in the row where Cat is in Col1 = 47
The row that Cat is in may vary.
....

A 2-way lookup. If your data were in B1:Z1 (numbers for which to find
max), A2:A100 (animals to match), and B2:Z100 (values to return), try

=INDEX(B2:Z100,MATCH("Cat",A2:A100,0),MATCH(MAX(B1 :Z1),B1:Z1,0))

or

=VLOOKUP("Cat",A2:Z100,1+MATCH(MAX(B1:Z1),B1:Z1,0) ,0)

or

=HLOOKUP(MAX(B1:Z1),B1:Z100,1+MATCH("Cat",A2:A100, 0),0)