Find the string and get data
I have 2 excel worksheets.
Sheet 1
Column A = ID number
Column B = Name
Sheet 2
Column A = Name
Column B = ID Number
Sheet 1 is database for all the ID numbers and name in the system And in sheet 2, I just want the Id numbers for the name already typed before. I don't开发者_StackOverflow社区 want to use any macros.
This solution is based on dynamically assigned name fields and INDEX/MATCH. The dynamically assigned named area will automatically increase when you add additional ID and names in it.
1. Create 2 named areas.
Name 1:
Define names -> Name manager -> New
Name: myID
Referes to: =OFFSET(Sheet1!$A$1;0;0;COUNTA(Sheet1!$A:$A);1)
Name 2:
-> New
Name: myNames
Referes to: =OFFSET(Sheet1!$B$1;0;0;COUNTA(Sheet1!$B:$B);1)
2. Insert formula to fetch the ID number.
In cell B2: =INDEX(myID;MATCH(A2;myNames;0);1)
Copy this formula down column B ...
精彩评论