XLOOKUP: The debate is over.
As many of you reading this article will know, 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?
Learn more about the PerfectXL tools
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:
- Select a cell.
- Type =VLOOKUP( and then select the value to lookup.
- Type a comma (,) OR semicolon (;) and select the range or table to look for the value.
- Type a comma (,) OR semicolon (;) and the number of the column where the lookup value is located.
- Type ,FALSE) to find an exact match.
- 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.
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:
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, 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.
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:
- Select a cell.
- Type =XLOOKUP( and then select the value to lookup.
- Type a comma (,) OR semicolon (;) and select the column/range where the value should be found.
- Type a comma (,) OR semicolon (;) and select the column/range where the value you want returned should come from.
- Press Enter.
Note: 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.
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.
We provide quality software like PerfectXL Risk Finder to businesses worldwide. Are you interested in learning more? Then plan a demo!
Industries we work with
Transport & logistics
PerfectXL Excel Solution in Transport & Logistics Transport and logistics companies require precision, planning, and lots of data, which makes Excel an obvious tool. PerfectXL makes Excel perfect, reducing risks,Read more here
PerfectXL for Excel files in Commercial Businesses Commerce involves money, money needs to be accounted for and tracked. This process often occurs in Excel, do you really want that informationRead more here
Accounting & Consulting
PerfectXL Excel Solutions for Accountants & Consultants Whether you work for one of the big 5 or a smaller company in finance in consulting, PerfectXL can soon become unmissable forRead more here
PerfectXL Excel Solutions for Insurance Companies Insurance companies are filled with large, complex calculation models. These are often built in Excel, and we aim to make Excel perfect, reduce theRead more here
Banking & Investment
By Industry PerfectXL Excel Solutions for Banking & Investment If you work in banking or investment, we want to help you develop a manageable audit system which will save youRead more here