Toggle Between VLOOKUP, XLOOKUP, and INDEX/MATCH (Shortcut)

Overview

Lookups are some of the most crucial and helpful functions that Excel and other spreadsheet programs have to offer. There are several options that can return the same result.  So which one is the best?  It’s really up to to what you’re comfortable with.  Do a quick search online and you’ll probably find polarizing opinions with lots of debate in the comments.  That’s not the point of this article.

In this article, we’ll walk the syntax of each approach, the pros and cons, and in the video below, we’ll look at a tool I built to help learn the syntax and quickly toggle between them!  Make sure to grab a copy of the example workbook containing the toggle lookup tool that you can use for yourself.  To have that available to all of your files, along with hundreds of other productivity tools, check out the XLEV8 Excel Add-in!

Using VLOOKUP

VLOOKUP is the most widely-used approach to applying a lookup – referencing data in another area, sheet, or file based on some lookup value.  It’s almost a rite of passage.  You’ll often see it on resumes as an “advanced Excel skill” but honestly, I think it’s table stakes these days.

Main gotcha: the column reference for the value to return is static, so structure changes could break the formula (though there are workarounds).

Here’s the syntax for VLOOKUP:

=VLOOKUP(lookup_value,table_array,column_index_num,[range_lookup])

And here’s how each of those parameters work:

1. lookup_value – the value to look for in the first column of a table
2. table_array – the table (tabular range of cells) from which to retrieve a value
3. column_index_num – the column in the table from which to retrieve a value
4. range_lookup – [optional] TRUE = approximate match (default), FALSE = exact match

Here’s a screenshot of a VLOOKUP formula with the referenced cells. I generally reference more columns than I need in the table array in case the formula will be copied or updated.

Using XLOOKUP

XLOOKUP is the new kid on the block. It was released with the initial wave of dynamic array functions that can spill automatically across multiple cells. Although it’s mostly used for vertical lookups, it is flexible enough to handle horizontal lookups as well. And because it separately references the lookup range and the return range, it will still work if columns are inserted between them.

Main gotcha: because it’s new(ish), many users may not have a version that supports it.

Here’s the syntax for XLOOKUP:

=XLOOKUP(lookup,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])

And here’s how each of those parameters work:

1. lookup – the value to look for
2. lookup_array – the array or range to search/li>
3. return_array – the array or range to return
4. if_not_found – [optional] Value to return if no match found
5. match_mode – [optional] 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match, 3 = regex match
6. search_mode – [optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending

Here’s a screenshot of an XLOOKUP formula with the referenced cells.

Using INDEX/MATCH

INDEX/MATCH is often considered the expert approach to lookups. It can overcome some of the limitations with VLOOKUP, namely searching columns to the left of the lookup column. It can also handle horizontal lookups and advanced approaches like multiple criteria and two-way lookups.

Main gotcha: it requires both the INDEX and MATCH functions, which can often trip up users that don’t use them that often.

Here’s the syntax for INDEX and MATCH when combined for lookups:

=INDEX(return_array,MATCH(lookup_value,lookup_array,[match_type]),[col_num],[area_num])

And here’s how each of those parameters work:

1. return_array – the array or range to return
2. lookup_value – the value to look for
3. lookup_array – the array or range to search/li>
4. match_type – [optional] 1 = exact or next smallest (default), 0 = exact match, -1 = exact or next largest
5. col_num – [optional] the column position in the reference or array
6. area_num – [optional] the range in reference that should be used

Here’s a screenshot of a formula using INDEX and MATCH with the referenced cells.

Toggling between (shortcut!)

Prior to XLOOKUP, I used VLOOKUP 99% of the time.  When XLOOKUP came around, I immediately liked it, but I had to be careful because not everyone I worked with used a compatible version of Excel.  It also slowed me down a little because I wasn’t used to the syntax quite yet.

To help learn it and quickly change my existing VLOOKUP formulas, I made a macro that parses the formula contents and toggles the references between VLOOKUP, XLOOKUP, and INDEX/MATCH.  Check out the animated image or video below, or better yet, grab a copy of the workbook and use it for yourself!  It’s also included with the hundreds of tools in the XLEV8 Excel Add-in.  To learn more about it, you can also check out the support page.

Video

Summary

If you’ve never taken the time to learn any of the lookup functions/approaches available in Excel and other spreadsheet programs, you’re really limiting yourself on what you can do.  They are crucial for combining data and efficiently managing workbooks.  It’s worth trying out each of the approaches covered above to know how they work for when you need to collaborate with others (or take over their work!).  And take advantage of the lookup toggle tool in the accompanying free workbook to quickly switch them out!

What’s your favorite lookup approach?  Has it changed over time?  Let us know in the comments below!