Find the string and get data

Question

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 want to use any macros.

Answer 1

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 ...