Using XLOOKUP in Excel

Have you ever needed to sort a list in Excel? I had a three tab workbook recently where I exported data from a forum on one tab; then used a Pivot table on a second. Then on a third, I wanted to re-sort the Pivot table results into a different order so I could import some numerical data back into another spreadsheet.

And just to make life more complicated, I didn't want to link the data as I do all sorts of portable and remote things, and links do tend to rot. Well, break.

In the Pivot table, I had a list of names in column A. In column H I had the data that I wanted to copy back out. While I could copy that data with an ='Pivot table'!A$6, and a 'Pivot table'!H$6, that would still mean that I needed to sort the data. And the data would be updated many, many times.

What I really wanted was to copy in a list of the names in the other spreadsheet order, then have Excel map the names and the data from that point on.

I spent HOURS trying to get this simple pick up to work. I tried VLookups, Matches, Indexes, HLookups. All were a mega-fail. But thanks to Dave Bruns (2019), I discovered XLookup. And now my data pick up works PERFECTLY.

I copied my names out of the other, unlinked spreadsheet, which is in the order I wanted my data to be organised. Then, to pick up the name in this ‘copying’ sheet, I set up an XLookup formula. This is designed to pick up and match the name (NB: while I don't need to repeat the name, it is easier to double-check a name than a number):

=XLOOKUP(A3,'Pivot table'!$A$6:$A$40,'Pivot table'!$A$6:$A$40,FALSE)

So the formula contains (a) the XLookup function; then (b) the cell I want the Pivot table data to be matched to in the ‘copying’ sheet order; then (c) the Pivot table data column range where it should search to find the match; then (d) the Pivot table data column range where it should pick up the match from; then (e) what to do if it can't find a match.

Then my data column is picked up and pulled through in my desired order with:

=XLOOKUP(A3,'Pivot table'!$A$6:$A$40,'Pivot table'!$H$6:$H$40,FALSE)

That is the same again, but we can see that this time, the formula for (d) looks in column H for the match, as that is where the data is that I want picked up.

And now I have my name and the forum data, ready for copying out. And it works beautifully. 

However, if I wanted a simple exact match, I could simply use:

 

 

It works beautifully.

Sam

References:

Bruns, D. (2019, September 06). XLOOKUP Function. ExcelJet. https://exceljet.net/functions/xlookup-function