EXCEL BLOG

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:

 

  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.

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)”

 

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:

 

  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.

 

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!

Share post :

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

Commercial

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 information

Read 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 for

Read more here

Insurance

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 the

Read 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 you

Read more here

How can PerfectXL help you?

Is there anything we can do to help you? Do you have any questions about the content of this blog post or our software? Don’t be afraid to reach out, we’re happy to chat!

Continue reading

Go to blog overview

Keep your inspectors happy with good & reliable Excel models

Excel is, without a doubt, the most user friendly program in the world for making calculations and that is why you will find Excel models in almost every organization. Nearly a billion people worldwide use Excel; in private situations, with small companies, large companies and with many governmental organizations.

Learning on the job

Many of you want to get better at Excel, but you can’t find the time to follow a course. Or the exact opposite, you have time to follow a course, but you’re afraid that you’ll forget what you learned in a matter of weeks because you aren’t going to apply the learnings right away. In either case we would advise you to follow a course when it’s busy, not when you have a ton of free time!

Oz Du Soleil: Bringing joy and excitement to Excel

Oz du Soleil, an Excel MVP, is a true Excel hero and easily worthy of wearing his underpants on the outside of his pants… a true superhero in the Excel communities. His superpower is bringing charisma, storytelling, and flare to Microsoft Excel. From videos, to training, to presentations, he continues to bring a sense of excitement to something that many find dull. I got the chance to call him and ask him some questions relating to his interactions with the wider Excel community.