Name associated with a number

[Ken Schmidt]

I have a set of data that alternates names and numbers within a row. For
example: Smith, 7, Jones, 5, Peters, 2, etc. I'm trying to pull out of
each row the name associated with a specified number. So if the specified
number is 2 in the example, I want the name Peters pulled out. The numbers
are unique within each row, so there will be only one possible choice. I
got a partial solution using the formula =MATCH(2,J3:AA3,0). This returns
the position in the range where the number 2 is found. But I've been unable
to display the name just before the 2 in the row. I tried OFFSET, but
couldn't get it to work right. I would appreciate any help. Using Excel 97.

Ken

 

[Don Guillett]

just add to the match to get the row and then use OFFSET or look in HELP for
INDEX.

 

[Max]

One way ..

Assume the data below is in J3:O3 :
Smith, 7, Jones, 5, Peters, 2 (etc)

Put in say, K2: =OFFSET($J$3,,MATCH(J2,$J$3:$AA$3,0)-2)
Input the number in J2

If J2 contains: 2, K2 will return: Peter
Input 7 in J2, K2 returns: Smith

Perhaps better with an error trap, put instead in K2:
=IF(ISNA(MATCH(J2,$J$3:$AA$3,0)),"",OFFSET($J$3,,MATCH(J2,$J$3:$AA$3,0)-2))

which'll return blank: "" for any unmatched numbers

 

[Max]

Minor typo:

If J2 contains: 2, K2 will return: Peter

"Peter" should read as "Peters" in the above line

 

[Ken Schmidt]

Max:

Your formula worked. Thank you very much!

Ken

 

[Max]

Glad to hear, Ken !
Thanks for the feedback