XLOOKUP in Excel and Google Sheets

Try using mapflo for data analysis. It’s great for joining datasets. Get Started for Free.

Summary

XLOOKUP finds a value in one column and outputs the value in the same row in a different column.

XLOOKUP is one of several formulas that allow you to join data sets together and is more versatile than VLOOKUP.

Unlike VLOOKUP, XLOOKUP specifies the lookup column and result column, rather than the whole data range and index – so doesn’t require the look up column to be furthest left in the range.

The formula is: =XLOOKUP(search_key, lookup_column, result_column, [if not found],[match_mode],[search_mode])

Where:

• search_key => “find this value”
• lookup_column => “in this range of cells”
• output_column => “output the value in the corresponding row in this column”
• [if not found] => “if not match then return this value (if not specified then retuns #N/A”
• [match_mode] => “where 0 means exact match only. -1 means return the next smallest item; 1 means return the next larger item
• [search_mode] => “1 (default) search from the top down; -1 search from bottom upwards.”

CONTENTS

• Summary
• Introduction
• XLOOKUP example based on one value
• XLOOKUP example based on two values

INTRODUCTION

XLOOKUP works the same in Google Sheets and Excel

XLOOKUP finds a value in a column and outputs the value in the same row but in a different column.

The formula is: =XLOOKUP(search_key, lookup_range, result_range, [if not found],[match_mode],[search_mode])

The search key can:

• be a string (it needs inverted commas around it) e.g. =xlookup(“Tops”,A2:A5,b2:B5,0,1)
• be a number, e.g. =xlookup(5,A2:A5,b2:B5,0,1)
• be a reference, e.g. =xlookup(E5,A2:A5,b2:B5,0,1)
• use the wildcards question mark (?) and asterisk (*). Question mark standing in for a single character and asterisk standing in for any series of characters. To match an actual question mark or asterisk then prefix a tilda (~) before the the character and an extra tilda if you are trying to match a search_key with an actual tilde in it.

XLOOKUP EXAMPLE BASED ON ONE VALUE

Below is a simple data table showing Items Sold by Product Category:

If we want to find out how many Tops were sold then we can use the following formula:

=xlookup(“Tops”,A2:A5,B2:B5,”not found”,0,1)

• search_key => “Tops”
• lookup_column => A2:A5
• output_column => B2:B5
• [if not found] => “not found”
• [match_mode] => 0
• [search_mode] => 1

The formula outputs 40 [the number of Tops sold]

XLOOKUP EXAMPLE BASED ON TWO VALUE

We have Items Sold by Month and Product Category and in a separate table Revenue by Month and Product Category. How do we use XLOOKUP to combine the data into one table?

We can create a unique value by concatenating (joining together) Product Category and Month.

The concatenate formula is simple, you use an & between cell references. See below:

Now there is a unique value for each row of each table that looks like ‘SandalsJan-22’

We can use this unique row to do the XLOOKUP:

Using XLOOKUPS to join datasets

You can use XLOOKUPs combined with concatenate to join datasets. You need to use concatenate to join together dimensions that together create unique values (like Month and Product Category in the example above). You can then vlookup the concatenated value in the secondary dataset to append values from that dataset to the initial data table.

Mapflo makes joining data sets really 

mapflo makes joining datasets really easy.

instead of using concatenate and XLOOKUP you can select one or more columns that you want the values to match to create the join. All other columns will be automatically joined to the original dataset (and you can use Data Shaper Node to re-order or remove columns).