academy

Categories

AcademyFunctionsUsage & tipsXLOOKUP: the debate is over

XLOOKUP: the debate is over

XLOOKUP is the newest lookup function in Excel. It joins INDEX & MATCH, VLOOKUP, and HLOOKUP as the newest weapon in the arsenal of formulas used to masterfully locate and supply data across models. The real question for any serious user is… why should you use XLOOKUP?

Why you should use XLOOKUP

To answer this question it’s good to quickly analyze the current popular lookups. The most popular being, VLOOKUP. Let’s take a look at the syntax from Microsoft’s own website:

  1. Select a cell.
  2. Type =VLOOKUP( and then select the value to lookup.
  3. Type a comma (,) OR semicolon (;) and select the range or table to look for the value.
  4. Type a comma (,) OR semicolon (;) and the number of the column where the lookup value is located.
  5. Type ,FALSE) to find an exact match.
  6. Press Enter.

Pretty straightforward, except for point 5. You see if you type TRUE you will get what’s called an approximate lookup. This a very particular setting that only works in an ordered table, and generally is not what you want, because it would make sense that you’re looking for an exact match in most cases. This can be very annoying, and also, if you omit that last argument, the formula will automatically look up approximately. Another issue is that with the column number as an index, adding or subtracting a column from your original table can make your function look in the wrong column. Also if you expand your table, the VLOOKUP function will not automatically expand the range in which it searches.

Shortcomings of older methods

As is hopefully clear, this function has quite some shortcomings, the alternative for a lot of users has been a combination of the INDEX and the MATCH functions. This has one similar shortcoming to VLOOKUP in that it has a pesky approximate lookup value that is also set to find “the largest value that is less than or equal to lookup_value.” It does a better job with changes in the dataset, but the bigger issue with INDEX and MATCH is that it just looks and feels a bit more complicated:

=INDEX(C3:E11,MATCH(H2,B3:B11,0),2)

Advantages of XLOOKUP

So, that brings us to the newest of the lookups, XLOOKUP. Here are the advantages:

  • Standard lookup is an exact match, this is generally what you want, the other advantage here is that if no exact match is found you can return a value higher or lower if that’s what you want.
  • The syntax is super straight forward “=XLOOKUP(lookup_value, lookup_array, return_array)” it’s just what you want and it works like a charm.
  • Unlike VLOOKUP (and HLOOKUP), XLOOKUP can return an array with multiple items,” this is very cool, like most new Excel functions (dynamic arrays) this makes life a lot easier for you. In case you need 2 values, you can do that with just one function.
  • You can also add an “if not found” value which gives you a nice little text or value if the thing you’re looking for wasn’t found.
  • There is a [search_mode] option which lets you decide how you want to search. This is not important for smaller models, but if you have millions of data points, the difference between searching top to bottom or bottom to top can be huge.

Syntax of XLOOKUP

It’s pretty easy to see that XLOOKUP definitely should be your new go to for all lookups in Excel. It performs very well, it’s easy to understand, and it just saves you time and effort. For reference here is the syntax of XLOOKUP:

  1. Select a cell.
  2. Type =XLOOKUP( and then select the value to lookup.
  3. Type a comma (,) OR semicolon (;) and select the column/range where the value should be found.
  4. Type a comma (,) OR semicolon (;) and select the column/range where the value you want returned should come from.
  5. Press Enter.

N.B. INDEX & MATCH can still perform a bit better in cases where you are going to reuse the “MATCH” value several times as you only need to lookup the value once and can then apply it to any number of formulas and calculations.

PerfectXL can help

If you’re dealing with pesky lookups and difficult models, then PerfectXL is a great software bundle for you! Our PerfectXL Risk Finder automatically spots approximate lookups and numeric index references, like point 4 from VLOOKUP, and tells you how to fix them. Now you know another option is of course to replace that VLOOKUP with the far superior XLOOKUP.