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


CONTENTS

  • Summary
  • Introduction
  • XLOOKUP example based on one value
  • XLOOKUP example based on two values (concatenate function)
  • XLOOKUP based on two values (using an array)

Summary

XLOOKUP finds a value in a lookup column and then outputs the value in an output column (but same row).

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 a column index – so unlike VLOOKUP it 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 largest item
  • [search_mode] => “1 (default) search from the top down; -1 search from bottom upwards.”

XLOOKUP is useful for joining data from two separate tables. However if you are joining datasets regularly then it is quicker and easier to use mapflo – see the step by step guide below. Mapflo is particularly good for joining multiple columns and updating datasets with fresh data:

[insert mapflo video]

 

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 CRITERIA

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 use Boolean logic within an array (read more on how to do this here) or use the concatenate function to create unique values from two criteria (explained below).

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