SUMIFS? Really?
Why is SUMIFS included among those lookup methods? I’ll explain in a few minutes.
But first, let’s look at the lookup methods shown in this figure.
(You can download the example workbook here.)
The VLOOKUP Function
The VLOOKUP function probably is Excel’s most widely used lookup function. However, it’s seldom the best lookup function to use, as we’ll see shortly.
It has this syntax:
VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )
And here’s its formula in the figure:
F2: =VLOOKUP( SKU, Prices1, 2 )
The LOOKUP Function
The LOOKUP function was included in the first version of Excel to be compatible with Lotus 1-2-3. Even so, it often can work well.
Here’s its syntax…
=LOOKUP( lookup_value, lookup_vector, result_vector)
…and it requires the lookup_vector to be sorted in ascending order.
And here’s its formula:
F3: =LOOKUP( SKU, SKUs, Names )
In Microsoft’s help topic for the LOOKUP function, we learn that VLOOKUP is a “much improved version of LOOKUP.” That’s not always true, as you’ll see shortly.
The XLOOKUP Function
You’ll find the XLOOKUP function in Excel 365 and in any version of Excel after 2019.
If your version of Excel includes this function, it’s now the most-powerful lookup function for you to use…with one exception of a special case, which we’ll get to in a few minutes.
Here’s its syntax:
=XLOOKUP( lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode] )
And here’s its formula in the figure:
F4: =XLOOKUP( SKU, SKUs, Names )
That is, this formula says: Look up a SKU in the SKUs column and then return the corresponding item from the Names column.
The MATCH Function
This is another lookup function that’s been in Excel since its earliest years. Its purpose is to look up a value in a column and return the row-index number of that value within the column. (It also looks up values in rows.)
That is, if the searched-for value is the third value in a columnar list, MATCH returns 3. In the figure, for example, its formula in cell F5 returns 3, because “S103” is in the third row of the SKUs column.
Its syntax is:
=MATCH( lookup_value, lookup_array, match_type )
And its formula in the figure is…
F5: =MATCH( SKU, SKUs, 0 )
…where the match_type of 0 tells the function to search sequentially and return an #N/A if the value isn’t found.
The INDEX Function
The INDEX function isn’t a lookup function. Instead, it returns the value in a specified range at the intersection of a specified row and column number.
As an Excel program manager told me in the early days of Excel, INDEX and MATCH were designed so they could work together.
The INDEX syntax is:
= INDEX( reference, row_num, [column_num], [area_num] )
And its formula in the figure is…
F6: =INDEX( Names, F5 )
…where the MATCH function in cell F5 specifies the row to return.
The INDEX-MATCH Formula
INDEX-MATCH formulas use the INDEX and MATCH functions in one formula, rather than in two formulas, as we did in cells F5 and F6.
The formula in the figure is:
F7: = INDEX( Names, MATCH( SKU, SKUs, 0 ) )
Compare this formula to the XLOOKUP formula…
F4: =XLOOKUP( SKU, SKUs, Names )
Notice that the XLOOKUP’s arguments are in the MATCH-INDEX sequence. That is, XLOOKUP starts with the two arguments found in MATCH and ends with the argument used by the INDEX function.
Sort the SKUs in Descending Order
In this example, I’ve sorted the Table by putting the SKUs in descending order.
Notice the conflicts in column F. Specifically, you can see that both VLOOKUP and LOOKUP return incorrect results. That’s because they both are using an approximate match that works only when SKUs are sorted in ascending order—not descending order, as in the figure.
We can fix the VLOOKUP formula easily by changing it from…
F2: =VLOOKUP( SKU, Prices18, 2 )
…to…
F2: =VLOOKUP( SKU, Prices18, 2, FALSE )
…where the FALSE for the range_lookup argument tells Excel to use sequential search and an exact match, which causes it to return #N/A when the lookup_value isn’t found.
But there’s no similar way to fix LOOKUP, unfortunately.
Look for a Missing SKU
In this example, the SKUs are back in ascending order. But now we’re looking for a SKU that’s not in the Table.
Because both VLOOKUP and LOOKUP are using approximate matches, they’re returning incorrect results again. You can see their formulas here:
F2: =VLOOKUP(SKU, Prices2, 2)
F3: =LOOKUP(SKU, SKUs, Names)
F4: =XLOOKUP(SKU, SKUs, Names, “SKU?”)
F5: =MATCH(SKU, SKUs, 0)
F6: =INDEX(Names, MATCH(SKU, SKUs, 0))
Notice that because we used XLOOKUP’s ability to specify an error code, cell F4 is returning “SKU?”. And both formulas using MATCH are returning the correct default result of #N/A.
Rearrange the Table’s Columns, #1
When we rearrange the Table’s columns, only the VLOOKUP formula returns an incorrect result.
This is because the VLOOKUP formula has been written to return the value from the second column, as you can see in the formula for F2 above. But in this case, the names are in column 3.
The other formulas return correct results, even the LOOKUP formula. This is where the LOOKUP function is more reliable than the VLOOKUP function.
Rearrange the Table’s Columns, #2
The VLOOKUP formula returns a somewhat mysterious result in this case. Here, the function uses an approximate match to search for “S103” in the Names column. It finds the row with Coats and returns its price from from the second column.
But meanwhile, XLOOKUP and INDEX-MATCH continue to return reliable results, no matter how we rearrange the data.
So, for the normal kinds of lookups, here’s the bottom line:
If your version of Excel offers the XLOOKUP function, always use it. The function is much less error prone than VLOOKUP and VLOOKUP, it’s easy to use, and it’s shorter than INDEX-MATCH. Also, XLOOKUP has several additional arguments that I haven’t explored in this article. You can learn about them here.
If your version of Excel doesn’t offer XLOOKUP, use INDEX-MATCH instead. It’s much more reliable than either VLOOKUP or LOOKUP.
Multi-Criteria Lookups
None of Excel’s normal lookup functions allow us to return values from a Table with more than one criterion.
So how do we do it?
We use the SUMIFS function, of course!
Here’s its syntax:
=SUMIFS(sum_range, criteria_range, criteria, …)
To use it as a lookup function for numeric values, we specify the range we want to look up, and then we can use any number of pairs of criteria_range / criteria arguments needed to make sure we return only one result from the SUMIFS formula.
The first formula shows the easy version of this method:
H4: =SUMIFS(Prices, SKUs,SKU, Regions,Region )
Here, to return the price for a specific SKU in a specific Region, we specify the Prices range as the sum_range. The first criteria pair filters for the S103 SKU, and the second pair filters for the West Region. Because there’s only one value that meets those criteria, we have one result.
But SUMIFS can’t return text. So how can we use it for that purpose?
Easy. The formula in cell H5 is logically similar to the formula in cell F5 in the Prices1 examples at the top of this article.
That is, both formulas return the row-index number for our lookups.
MATCH returns the row-index value automatically. But when we use SUMIFS for that purpose, we must return the “sum” of an Index value, as shown here in column A. This column must always return the same values no matter how the Table is sorted.
Here’s its formula:
A3: =ROW()-ROW(A$2)
And here’s the formula for the MATCH equivalent:
H5: =SUMIFS( Index, SKUs, SKU, Regions, Region )
That is, this formula returns the “sum” of the single value in the Index column that remains after we filter the SKUs and Regions columns.
And finally, to complete the INDEX-MATCH analogy, we have our INDEX-SUMIFS formula like this:
H6: =INDEX( Names, SUMIFS( Index, SKUs, SKU, Regions, Region ) )
The logic between the INDEX-MATCH and the INDEX-SUMIFS formulas is identical. We use the second function in either case to return the index-row number where our data can be found and then we use the INDEX function to return the data we want from anywhere in that row.
So the bottom line is this: For single-criteria lookups, use XLOOKUP or INDEX-MATCH. For multi-criteria lookups, use INDEX-SUMIFS.
