MATCH Formula

S

Sandy

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!
 
S

Sandy

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
 
H

Harlan Grove

Sandy wrote...
Sorry one slight typo Max of b1:z1 and match A2:Z23
An abbreviated example...
Click to expand...
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.
Click to expand...
....

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)
 
Top