Finding Data with XLOOKUP

LOOKUP Functions: XLOOKUP

Excel has replaced 2 antiquated functions for looking up data – VLOOKUP and HLOOKUP – which, respectively, allowed you to do a vertical search and a horizontal search through a list of records. They worked by asking you to select the entire range of your data, look for a value within that data, and then choose which column from within the data (or row, if it was a horizontal or HLOOKUP function) to return in response to the lookup. They both also offered the opportunity to choose whether or not the match was exact or not, and frankly, anything but an exact match didn’t work. It wouldn’t find similar spellings, for example, though by making exact or not an option, implied that it did.

Replacing those 2 functions – though they still exist, for people who already have them in place and want to keep using them – is XLOOKUP, which works for both vertical and horizontal searches and has fewer hoops to jump through in terms of providing all the arguments for the function. It also assumes you want an exact match, and it always works.

I’ve got an XLOOKUP set up here, in a form for use in a school where kids can look up bugs in their own class database and see where they were found and how many were seen. We’ll look at the function and see its arguments, and then build our own using another table.

Here on the XLOOKUP tab, if I type a new bug name into cell C7, you’ll see the two cells below that repopulate. Those are the cells where the XLOOKUP functions, one per piece of data, live. Here’s the first one, in cell C8.

The arguments are:

Lookup Value. That’s the cell where the value to look for – in this case, a bug name – is found.

Lookup Array. Here’s where you put the range of cells containing the values you might enter into the Lookup value cell. For the bug names, this is in column A in the source data.

Return Array. This is the column – or row – where the data you want to be shown after entering a lookup value, is found. For the Location Seen lookup, this is in column B. The second XLOOKUP, which answers the question “How many?” is found in column C.

The remaining arguments – if not found, match mode, and search mode – are optional, and I haven’t used them, as you can see. We’ll look at them in more detail when we build our own XLOOKUP from scratch.

So now let’s build our own.

Opening the Insurance Policies worksheet, let’s look at what we could set up. We’ll use the XLOOKUP sheet to build it, but looking at the data we’ve got stored, if someone supplies the policy number, we can set up XLOOKUP to retrieve the policy holder’s name, the amount of their policy, and what type of building they’re insuring – among other items. Anything you’re storing can be retrieved with an XLOOKUP.

So, now on the XLOOKUP tab, I’ve got a form laid out already, just to tell people using it where to enter the policy number and what info they’ll be able to retrieve. We’ll build the functions that populate the Insured First and Last Name fields and the Insured Value.

In cell D6, type:

=XLOOKUP( (press TAB to insert the opening parenthesis), and then tell Excel where the lookup value will be found – that’s in cell D5.

Then, type a comma.

And then go to the Insurance Policies sheet and drag through cells A6 through A505, where the policy numbers are stored. Then type a comma.

Then, still in the Insurance Policies tab (you can see the formula building on the Formula Bar), drag through cells B6 through B505, to grab the cells containing the first names – the value you want to return then the lookup value is found.

Then close your parentheses.

Your formula should appear as =XLOOKUP(D5,'Insurance Policies'!A6:A505,'Insurance Policies'!B6:B505),

and if you type any policy number into cell D5, such as 101102, we see the information populate in cell D6.

Repeating this for the remaining cells D7 and D8, we’re using the same range for the lookup array, but each one has a different return array – the range containing the info you want to retrieve.

Once the form on the XLOOKUP tab is completed, you could make the sheet available to anyone who needs to look up policy information, without forcing them to sort and filter in the policy table itself. If people request additional lookups, you can always add them – and now you know how to do it!