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

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

--
Don Guillett
SalesAid Software

"Ken Schmidt" wrote in message
ink.net...
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

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,,M ATCH(J2,$J$3:$AA$3,0)-2))

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Ken Schmidt" wrote in message
ink.net...
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

Minor typo:

If J2 contains: 2, K2 will return: Peter
"Peter" should read as "Peters" in the above line

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Max:

Your formula worked. Thank you very much!

Ken

Max wrote in message
...
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,,M ATCH(J2,$J$3:$AA$3,0)-2))

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Ken Schmidt" wrote in message
ink.net...
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

Glad to hear, Ken !
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Ken Schmidt" wrote in message
nk.net...
Max:

Your formula worked. Thank you very much!

Ken