|
Name associated with a number
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 |
| All times are GMT +1. The time now is 10:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com