countif

The Excel Table Lookup Function Guide: XLOOKUP vs VLOOKUP vs INDEX & MATCH vs SUMIF(S) vs COUNTIF(S)

Excel has several useful functions for retrieving and processing information from a table, but it can become confusing fast. This simple guide tells you when to use which. XLOOKUP (for Office 365) and INDEX & MATCH are superior for table lookup For looking up information from a table, XLOOKUP (for Office 365 users) or the combination of the INDEX & MATCH functions (for all previous versions of Excel) are superior to VLOOKUP or any of the other options listed above. This is because both XLOOKUP and INDEX & MATCH:

• Can retrieve both text and numbers (unlike SUMIF, SUMIFS or SUMPRODUCT);
• Can retrieve values that are sorted in either rows or columns (unlike VLOOKUP and HLOOKUP, which are limited to columns and rows, respectively);
• Are not sensitive to column insertions & deletions (unlike VLOOKUP); and
• Does not require the retrieval column to be to the right of the lookup column (unlike VLOOKUP).

For Office 365 users, XLOOKUP should be the go-to function when you want to look up information from a table. For earlier versions of Office or Excel, you will need to use the combination of INDEX & MATCH.

Most people shun INDEX & MATCH because it seems more complicated than the other functions. However, if you read on, I hope you’ll find it easier to use than you thought and you’ll add a powerful tool to your Excel toolbox.

INDEX & MATCH explained

I’d like to show you that INDEX & MATCH is not complicated at all. It combines two very simple functions.

Let’s have a look at each on its own, and then we’ll combine them.

INDEX

The INDEX function is a simple table lookup function. It retrieves a value from a range or array at the specified position. (Range refers to a single column or row, whilst array refers to a table with multiple columns and rows.)

Function syntax (square brackets indicate optional values. See end of post for more detail): INDEX(array,row_num,[col_num])

Here is an example to illustrate use of the INDEX function. MATCH

The MATCH function is a simple matching function. You specify a range (one row or column of values) and the value that should be matched, and the MATCH function will return the row or column number of the first instance of that value.

Function syntax (square brackets indicate optional values. See end of post for more detail): MATCH(lookup_value, lookup_array, [match_type])

Here is an example to illustrate use of the MATCH function. INDEX & MATCH together

When you combine the INDEX and MATCH functions, you get a flexible and powerful lookup function for any table. Use the MATCH function to determine the relative position in the lookup range of the value you are matching. This is then an input into the INDEX function to retrieve the corresponding value in the retrieval range.

Let’s work through a simple example to illustrate step-by-step how this works.

Say you have the same table as in the previous two examples: Suppose you want to look up the league points for a team that you specify. Let’s assume it is Tottenham Hotspur.

So you want to match “Tottenham Hotspur” with a value in the range C4:C13 in the table above. This would be achieved as per the MATCH example above, i.e.:

=MATCH(“Tottenham Hotspur”,C4:C13,0)

This will return the result 5.

Now we insert this into the INDEX function to look up the league points at position no. 5 in the league points range:

=INDEX(D4:D13,5)

This will return the result 21.

It really is as simple as that! Let’s look at the combined function to do the matching and retrieval in one go:

=INDEX(D4:D13,MATCH(“Tottenham Hotspur”,C4:C13,0))

This will return the result 21.

XLOOKUP is INDEX & MATCH all in one

The XLOOKUP function combines the INDEX and MATCH functions in one. First it matches a lookup value to the same value in a lookup array and then it returns a corresponding item in the return array.

The XLOOKUP function syntax is as follows (see end of post for more detail):

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

If you omit [match_mode], the default value is 0, which is an exact match.

Let’s apply the XLOOKUP function to the same football league points table example from above:

=XLOOKUP(“Tottenham Hotspur”,C4:C13,D4:D13)

This will return the result 21.

All the functions in action

Let’s look at a bigger example to see all the functions in action. Here is an expanded premier league table: Here is a set of questions with answers produced using the various functions. The best function for each question is indicated by a bold-formatted answer. And here is the set of formulas used to generate those answers: You will note that some functions require a bit of gymnastics to make them do something that they were not designed for. I won’t discuss how these work here, but leave a comment if you want an explanation for any specific formula.

Do you prefer a different function or approach, or you have a better way of doing it? Let me know in the comments.

If you found this post useful, the course Financial Modeling for Entrepreneurs 201: Kickstart Your Spreadsheet Skills, teaches you everything you need to build spreadsheet-based financial models.

Summary of function syntax

Here is a brief summary of the functions discussed in this post (you can find more detail on each at Microsoft Office Support or Exceljet):  Photo by Edi Libedinsky on Unsplash