For an in-depth explanation of the upcoming XLOOKUP function, see the post
“Excel XLOOKUP Explained (Goodbye VLOOKUP)“
Featured Course
Master NEW Excel Functions in Office 365 & Office 2021
The Issue at Hand
We have a list of employee names and we want to generate a list of employee IDs beside the names.
On a separate tab named “Master”, we have a table of the employee names, their IDs and departments.
The problem is that the column we are to discover the name (the ‘key’ column) is to the right of the ID column.
If we were to perform this lookup using the tried and true VLOOKUP function, we would have to rearrange the columns, so the employee name was to the left of the ID column.
But what if you can’t rearrange the columns?
XLOOKUP to the Rescue!
The logic of XLOOKUP works like so:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])NOTE: arguments in square brackets are optional.
- lookup_value – is the value we want to find (cell B4)
- lookup_array – is the list to find the lookup_value in (sheet “Master” cells C3:C33)
- return_array – is the list to return from upon discovery (sheet “Master” cells A3:A33)
- [if_not_found] – is what to display if no match exists (e. text message or default value)
- [match_mode] – specifies the Match Type. (0 = Exact match {default}, -1 = Exact match or next smaller, 1 = Exact match or next larger, 2 = Wildcard match)
- [search_mode] – specifies the Search Mode. (1 = Search first to last {default}, -1 = Search last to first, 2 = Binary search {ascending}, -2 = Binary search {descending})
For our example, we want to search for the name in cell A4 within the range of cells C3:C33 on the Master sheet. If a match is located, we wish to return a value from the same row of cells A3:A33 on the Master sheet.
=XLOOKUP(A4, Master!$C$3:$C$33, Master!$A$3:$A$33)IMPORTANT: Don’t forget to reference the lookup and return columns as absolute references (dollar signs). This way, the references won’t change when you fill the formula down to the other employee names.
“Gary Miller” exists on row 22 of the Master sheet and his ID is “ID4”.
When we fill the XLOOKUP formula down to the adjacent rows, we see the ID numbers for the remaining employee names.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
