Multiple Conditions in HLOOKUP in Google Sheets

If you want to look up values across a row and return a result from the same row or another row in the range, you can use the HLOOKUP function in Google Sheets. Of course, you could also use the LOOKUP function if the data is sorted by the first row in ascending order or the XLOOKUP function. If you’re more comfortable using HLOOKUP over the other two, you may find the advanced tip below helpful. Below, you’ll find examples of how to use multiple conditions in the HLOOKUP function in Google Sheets.

Sample Data

The sample data below shows the availability of different car types in a dealer’s various showrooms.

Sample data for applying multiple conditions in HLOOKUP in Google Sheets

Car types such as Hatchback, Sedan, and SUV are in row 1, available locations are in row 2, and available quantities are in row 3.

You can use the following formula to look up “SUV” in row 1 and return the location available in row 2:

=HLOOKUP("SUV", A1:G3, 2, FALSE)

To return both the available location and quantity, you can use this formula:

=ARRAYFORMULA(HLOOKUP("SUV", A1:G3, {2, 3}, FALSE))

What if you want to find the availability of the “SUV” car type in “Showroom 2”? In this case, you need two conditions: one for “SUV” and one for “Showroom 2.”

The HLOOKUP function doesn’t directly support multiple conditions. Therefore, we need a workaround approach.

Example of Using Multiple Conditions in HLOOKUP

In the previous example, you can use the following formula to apply multiple conditions:

=ARRAYFORMULA(HLOOKUP(1, {(A1:G1="SUV")*(A2:G2="Showroom 2"); A3:G3}, 2, FALSE))

This formula is essentially searching for “SUV” in row A1:G1 and “Showroom 2” in A2:G2 and returning the value from the third row in the range.

However, you might see the search key as “1”, the range as {(A1:G1="SUV")*(A2:G2="Showroom 2"); A3:G3}, and the column index as “2”. This may seem confusing unless you understand the formula breakdown, which I will explain below.

How the Multiple Conditions HLOOKUP Works in Google Sheets (Formula Breakdown)

The formula follows this syntax:

HLOOKUP(search_key, range, index, [is_sorted])

Let’s break down the formula:

  • Range: The range is {(A1:G1="SUV")*(A2:G2="Showroom 2"); A3:G3}.
    • (A1:G1="SUV"): This evaluates the first row for the value “SUV” and returns TRUE or FALSE.
    • (A2:G2="Showroom 2"): This evaluates the second row for the value “Showroom 2” and returns TRUE or FALSE.

When you multiply these two logical arrays, the result will be “1” wherever both conditions match, and “0” otherwise.

We combine this with the rest of the range (the vehicle availability data) using curly braces. The second row in the new range, A3:G3, contains the actual availability values, while the first row holds the logical results (1 or 0). Thus, the new range consists of only two rows.

  • Search Key: We specify “1” as the search key because we’re looking for where both conditions are true (i.e., the cells in both rows match the search terms).
  • Column Index: We specify 2 as the column index to return the value from the second row (which, in this case, is the available quantity).
  • Is Sorted: We specify FALSE as the last argument since the data is not sorted.

Conclusion

We have now covered how to use multiple conditions in the HLOOKUP function in Google Sheets. By using this method, you can perform more complex lookups with multiple criteria across rows.

Note: The ARRAYFORMULA function is required here because the logical test in the range needs to be evaluated across multiple values.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Hyperlink to Jump to the Last Used Row in Excel

In a vertical range, you can create a hyperlink to jump to the last...

Find the Last Used Row’s Last Value Address in Excel

In a large vertical dataset in Excel, how do you find the cell address...

Find the Last Used Row Number in Excel

When working with large datasets such as sales records, purchase data, or bills of...

More like this

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.