Can you do VLOOKUP to the left?

Left Lookup in Excel

with XLOOKUP (made easy)

When users learn to perform a lookup in Excel, it doesn’t take long before a situation arises that causes the user to ask…

“How do I perform a lookup to the LEFT?”

Because most users are using the VLOOKUP function when this occurs, the sad answer is, “You can’t.”

This is one of the limitations of the VLOOKUP function; all return items must be to the right of the lookup (or ‘key’) column.

But not anymore!

With Excel’s upcoming XLOOKUP function, looking up an item to the left of the ‘key’ column couldn’t be easier.

Let’s see how.

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.

Learn Excel from a Microsoft MVP

Check out my bestselling Excel Courses

Learn anytime that fits your schedule.

Download files. Practice. Apply.