XLOOKUP and VLOOKUP with arrays - the latest in spreadsheet gymnastics

Whether you’re in accounting, finance, or operations, you’ve seen the movie all too often of downloading multiple data sets and bringing them together in google sheets or excel with various lookups or joins. At Sudozi, we’ve built our fair share of models and wanted to put together our latest tips in combining different data sources for automated models. You can see all of our examples in this sample spreadsheet, including the excel download link.

VLOOKUP

This is the original lookup.

Pros: One of the most used formulas. Anyone with intermediate Excel knowledge will be able to follow what the formula is doing.

Cons: If the table’s structure is edited, such as someone adding an extra column inside the table you’re looking up, your formula will likely break – or worse, return the wrong column.  The lookup column always needs to be to the left of the return column, which can sometimes mean you have to wrangle your source table to adapt it to this formula.

How it works:

To set up a normal VLOOKUP, you can follow this syntax:

=VLOOKUP(lookup_value, range, index, [is_sorted])

lookup_value = the value you’re looking up.

range = the source table from which you’re pulling values. The search_key will be searched in the first column of this range.

index = the number of the column from which you want to retrieve the corresponding value. Example: If you wanted to lookup values in column A and return the corresponding value from column E, you would enter 5 to return the value from the fifth column.

is_sorted = optional. It’s always recommended to set it to False or Zero in order to force an exact match search (unless you’re specifically making a nearest-match search).

VLOOKUP (with arrays)

In Google Sheets, you can take advantage of arrays to address the main drawbacks of a VLOOKUP formula. In a nutshell, arrays let you build ranges that don’t have to follow the exact setup you have on your spreadsheet, giving you more flexibility.

Pros: Whenever the source data is moved, this formula moves with it. No need to worry about a new column breaking your lookup formula.  Also no need to keep counting the index number (the column number to retrieve). With this setup, the index is always 2 :).

Cons: Only works in Google Sheets. Some people might not have seen this method, so it becomes harder for others to audit and follow this formula.

How it works:

Setting this up is a lot like a normal VLOOKUP, with a twist:

=VLOOKUP(lookup_value, {lookup_array, return_array}, index, [is_sorted])

lookup_value = the value you’re looking up.

{lookup_array, return_array} = here, instead of selecting an entire range source table as usual, you simply select the lookup and return columns in { } brackets. Example: If you wanted to lookup values in column A and return the corresponding value from column E, you would enter {A:A, E:E} instead of A:E.

index = since your range is now just two columns, your index is always just 2.

is_sorted = optional. It’s always recommended to set it to False in order to force an exact match search (unless you’re specifically making a nearest-match search).

Index Match

This is the trusted method for those who want to avoid VLOOKUP’s pitfalls. 

Pros: Avoids the VLOOKUP’s issues of forcing the lookup column to be to the left of the return column, needing to count the number of columns, and risking that changes to the table’s order could break the formula.

Cons: Harder to follow the formula by more casual users.

How it works:

You can use this method by following this syntax:

=INDEX(return_array, MATCH(lookup_value, lookup_array, match_type))

return_array = the column from which you want to pull the corresponding value.

lookup_value = the value you’re looking up.

lookup_array = the column where you’re searching for the lookup_value.

match_type = optional. Set to False or Zero to force an exact match.

XLOOKUP

This is Excel’s more modern solution to these formulas, introduced in late 2019.

Pros: Very simple setup. Pick the lookup value, set the reference column, pick what you want to pull, and you’re done.  Whenever the source data is moved, this formula’s ranges update automatically.  This formula provides additional functionality for more advanced use cases, such as XLOOKUP with multiple criteria.

Cons: Only works in newer versions of Excel (Office 365 or Excel 2021 and newer).  Other users auditing the sheet may not be immediately familiar with the formula, though it’s arguably easier to learn and understand than other methods.

How it works:

Setup is simple:

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

lookup_value = the value you’re looking up.

lookup_array = the column where you’re searching for the lookup_value.

return_array = the column from which you want to pull the corresponding value.

For the optional arguments in brackets and how to use them, check out the full formula description.

If you look at enough models, you’re bound to have come across a few of these. Finance and Accounting teams are always ready to build new models. But once you have established processes and templates with other departments, it is best to move them into a tool to minimize errors. Transitioning these established processes to a tool also allows you to focus your energy creating models for strategic new initiatives. Sudozi helps companies digitize lightweight procurement processes as well as BvA reporting to give you more time to focus on high leverage items. Give us a try for free!