Formula to return employee codes from a lookup table of names and codes

Question

In Excel, I have five employees each with a unique emploee code.

In column A I have hundreds of rows of these employee names and I want to return each name's corresponding code in column B.

In columns D and E I have the unique list of employee names and their matching codes.

What formula could I use to return the codes in column B?

Sample Data

	A	B	C	D	E
1	Name	Emp code		Available data
2	User1	01Code		Name	Code
3	User2	02Code		User1	01Code
4	user4	04Code		User2	02Code
5	user1	01Code		user3	03Code
6	user5	05Code		user4	04Code
7	user3	03Code		user5	05Code
8	user2	02Code

Answer 1

Formula in B2

=VLOOKUP(DROP(TOCOL(A:A,1),1),D:E,2,0)

Explanation

1. VLOOKUP provides the basic functionality:

   VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
   

2. The lookup_value is DROP(TOCOL(A:A,1),1)
   • TOCOL returns Column A without any blank rows.
   • The DROP function is used to remove the heading row Name from the lookup_value.
3. The table_array is D:E
4. The col_index_num to return is 2 which is Column E.
5. The 0 (FALSE) specifies an exact match in case D:E is not sorted in ascsending order.