Battle of the Excel Lookup Functions: VLOOKUP vs INDEX/MATCH vs XLOOKUP
(Note: This article on Excel Lookup functions is suitable for Excel 2019, 2021 and Microsoft 365 users)
In this article, let us explore the three popular methods of performing lookups in Excel and see how they fare against each other.
You’ll learn:
Excel Lookup Functions – An Overview
Using a formula to look up information in a dataset is one of the most popular ways to analyze information in Excel. Excel lookup functions will search for a lookup value that is common between two tables and return a corresponding value from another column. It is very similar to how a phonebook works: You use it to look up a contact’s name and find the contact’s matching phone number from the data.
In the following example, we need to complete the table on the left and fill the empty columns, ‘Category’ and ‘Price’. We have the information we need in the table on the right, referred to as the lookup table.
The “Product” column is common to both the tables and hence is the lookup value. We use it to look up and return the corresponding ‘Category’ and ‘Price’ data from the main table.
We can perform these lookups using a few different formulas in Excel: VLOOKUP, INDEX/MATCH or XLOOKUP, but what is the difference between them? What are the advantages and disadvantages of each of these Excel lookup functions? Which one will work best for you?
In this ‘Battle of the Lookups’, we will explore all of them and it is up to you to decide on your ultimate champion!
Related:
COMPARE TWO LISTS USING VLOOKUP
VLOOKUP FOR DUMMIES (OR NEWBIES)
XLOOKUP GOOGLE SHEETS – 4 BEST ALTERNATIVES!
VLOOKUP
VLOOKUP is the most well known and popular of all the Excel Lookup functions. It’s flexible and easy to use, but it does have its own share of drawbacks.
Let’s use VLOOKUP to complete the ‘Category’ and ‘Price’ information for each product, in the following example.
- Click in cell F4.
- Type =VLOOKUP(
- Note the syntax and arguments.
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
| lookup_value | The value that is common between the tables |
| table_array | The table that contains the data (lookup table) |
| col_index_num | The column that contains the value to return |
| range_lookup | TRUE or FALSE (Exact or Approx.) match of the lookup value in the table (optional) |
In this example, the lookup_value is ‘TV’, the table_array is the lookup table range (F4 to make the cells absolute), the col_index_num is ‘2’ because we want to return the ‘Category’ and the range_lookup is ‘FALSE’ because we want to exactly match the word ‘TV’ in the table.
NOTE: The range_lookup argument is optional and has two possible inputs: TRUE or FALSE. TRUE means we are doing an approx. match of the lookup value in the table and FALSE means we are doing an exact match. If this argument is left blank, the default is TRUE.
- Double-click on the fill handle to copy the formula down.
Next, we need to complete the ‘Price’ column. The VLOOKUP formula is the same except this time the col_index_num is ‘3’ as we want to return the information in the third column. The range_lookup argument can be specified as ‘0’ for FALSE and ‘1’ for TRUE.
- Double-click on the fill handle to copy the formula down.
Drawbacks of VLOOKUP
The major drawback of VLOOKUP is its ability to only look from left to right in a table. The lookup value must always be to the left of the value to return or VLOOKUP will not work. When we construct a VLOOKUP, we specify a col_index_num which must always be positive.
For example, we could not use the ‘Category’ as the lookup_value and return the ‘Product’ because ‘Category’ is to the right of ‘Product’ in the table and we must specify a positive column number.
To perform a more flexible lookup, we would need to use an alternative method.
Suggested:
The Excel SMALL Function – 3 Best Examples
How to Make An Excel Forecast Sheet in 3 Steps – The Best Guide
How to Use Excel COUNTIFS: The Best Guide
INDEX MATCH
INDEX MATCH is technically not one of the Excel lookup functions, since INDEX and MATCH are two separate functions in their own right. But they are commonly used together to create powerful, flexible lookups when VLOOKUP isn’t quite cutting it.
The INDEX/MATCH combo can return a value from a table regardless of where the lookup value is. Let us see how to use this combo in the same example.
- Start by typing, =INDEX(
The first argument is an array. The array is the cell range that contains the value we want to return. In this example, it is the ‘Category’ data range: J3:J14
The next argument is row_num. We need to specify the row number that contains the value to return. In a small dataset, we could count down the items in the table but this is not efficient for large datasets. Instead, we use the MATCH function to automate the finding of the row number. We can do this by using the formula : MATCH(E4,$I$3:$I$14)
Here, the cell E4 is the lookup_value, i.e the value to lookup in the table
I3:I14 is lookup_array, i.e the cell range that contains the lookup_value
[match_type] is optional, specifies if Exact or Approx. match of the lookup_value in the table. Set it to 0, for exact match.
- Drag the fill handle to copy the formula down.
So, INDEX and MATCH are more flexible than VLOOKUP, but it’s a more complex formula to remember.
What if we have duplicate lookup values? Both VLOOKUP and INDEX/MATCH will return the first occurrence in the list of the lookup value. If we want the second occurrence, we need to think of an alternative lookup method.
XLOOKUP
XLOOKUP is the latest entry into the Excel lookup functions family. If you have ever used (and struggled with) INDEX and MATCH or VLOOKUP, to perform complex lookups, then XLOOKUP is for you. It is an easy to use, better alternative to other Excel lookup functions.
Like INDEX and MATCH, XLOOKUP can return values from any position in the table in relation to the lookup value but it can also search from the top or bottom which is useful if there are duplicate lookup values in the list.
- Type =XLOOKUP(
- Note the syntax and arguments.
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]
| lookup_value | The value that is common between the tables |
| lookup_array | The range that contains the lookup value |
| return_array | The range that contains the value to return |
| if_not_found | Text/value to display if the lookup_value is not found in the table |
| match_mode | Exact match, approx. match or wildcard match of the lookup_value |
| search_mode | Search from first to last, last to first or binary search |
In this example, we have ‘TV’ listed twice in the lookup table. VLOOKUP and INDEX/MATCH search from top to bottom and so will only ever return the first occurrence of ‘TV’ in the table. XLOOKUP can search from bottom to top. Also note, the product ‘Lamp’ has been removed from the lookup table. We can add text to display if the value is not found.
- Type =XLOOKUP(E4,$I$3:$I$13,$J$3:$J$13,”No Record”,0,-1)
The formula will look for the lookup_value (E4) in the lookup_array (I3:I13) and return the corresponding value in the return_array (J3:J13). If the value is not found in the table it will display the text ‘No Record’ in the cell. We are doing an exact match of the lookup value (0) and we are searching through the table from last to first (-1).
Suggested Reads:
How to Shade Every Other Row in Excel? (5 Best Methods)
How to Superscript in Excel? (9 Best Methods)
How to Split Cells in Excel? 3 Easy Methods
Closing Thoughts
In this article, we have looked at what VLOOKUP, INDEX/MATCH and XLOOKUP, the three popular Excel lookup functions have to offer. So, how do they compare?
| Lookup Function | What’s good? | What’s bad? |
| VLOOKUP | Easy to remember. Suitable for most scenarios. | Not very flexible. Can only lookup information to the right of the lookup value. |
| INDEX/MATCH | Powerful and flexible. Lookup information wherever it is in relation to the lookup value. | Quite a complex formula. Hard to remember. Searches from top to bottom only. |
| XLOOKUP | Easy to remember. Powerful and flexible. Choose a search mode, match mode and text to display if the value isn’t found. | Only available in the latest versions of Excel (2019, 2021 and 365). |
It’s up to you to determine which lookup method you prefer. Personally, I am a huge fan of XLOOKUP as it combines the best features of both VLOOKUP and INDEX/MATCH plus adds some additional functionality.
If you would like to read more high quality Excel-guides, please check out our free Excel resources section.
Want to learn more about Excel? Click here to access our advanced Excel courses with in-depth training modules. You can train your entire team in Excel and other business software, for a low one-time monthly fee here.
Deborah Ashby
Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom.She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP.She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.
