How to use XLOOKUP with Multiple Criteria

Google Sheets

In this vlog you will learn how to use the more powerful function XLOOKUP with multiple criteria in Google Sheets. Let’s checkout how to do it.


Table of content:

  • About XLOOKUP Function
  • How to the XLOOKUP function works
  • How to use XLOOKUP with multiple criteria with easy example and syntax
  • Video of XLOOKUP with multiple criteria
  • About the author

About XLOOKUP Function: 

XLOOKUP is a more powerful and advanced function in Google Sheets. It is used to lookup the value and return the correspondence result. The XLOOKUP can replace the older functions such as HLOOKUP, LOOKUP, INDEX-MATCH and VLOOKUP. With the help of XLOOKUP function you can easily find the value from the large set of the dataset.


How the XLOOKUP works:

Firstly, we will learn how the XLOOKUP works in Google Sheets.

  1. Lookup Value: Lookup Value is the value that you want to search in the specific raw dataset
  2. Lookup Range: Lookup Range is the range, where you wish to search a specific Lookup Value
  3. Return Value: Return value is the value that you want to get in return when criteria is met
  4. Match Mode (Optional): XLOOKUP has three Match Modes. Match mode is basically used to determine how the Lookup Value to be matched:
    1. 0 – Zero is use to find the Exact Match
    2. 1 – One is use to find the largest value that is closer to the Lookup Value
    3. -1 – Minus One is use to find the smallest value is closer to the Lookup Value
  5. Search Mode (Optional): XLOOKUP has two Search Modes. Search mode is basically used to search weather to search from the beginning or end in the Lookup Range
    1. 1 – One is use to search from the beginning of the Lookup Range
    2. -1 – Minus one is use to search from the end of the Lookup Range

If you wish not to use the “Optional” arguments, you can ignore these arguments. But if you want more accurate results it is always important to get the benefit of Optional arguments.


How to use XLOOKUP with multiple criteria:

Now, you will learn how to use XLOOKUP with multiple criteria in Google Sheets. Suppose you have raw data like below and you want to find the Price of the product based on the multiple criteria:

Raw Data

CategorySub CategoryColorPrice
ElectronicFridgeRed$198
ElectronicFridgeBlack$149
ElectronicMobileBlack$139
ElectronicLCDBlack$199

Criteria:

  • Category should be “Electronic”
  • Sub category should be “Fridge”
  • Color should be “Black”

Based on all above criterias, we should get the Price of the product.

Basic Syntax:

XLOOKUP Multiple Criteria

To Find the Price of the Black Fridge, you need to use a XLOOKUP function like below:

This formula will return the value as “$149”


Video of XLOOKUP with multiple criteria:

We have also created a video on how you can use XLOOKUP with multiple criteria and get the results. You can watch it on our YouTube channel. For more educational videos you can subscribe to our official YouTube channel.


About the Author:

Vishal Pooner has over 12+ years of experience in Digital Marketing and automating existing workflow using the Google AppScript. You can follow him on LinkedIn, YouTube, Instagram.

If you need our help to make productivity tools or automate existing workflows for your business. Please write an email to info@exceldose.com


Contact Info

Let's Chat!

Get in touch with us to discuss your needs. Write an email to us at