How to Use Multiple Conditions in Hlookup in Google Sheets

I have my own approach to using multiple conditions in Hlookup in Google Sheets. Because I am familiar with the same technique in Vlookup.

The below sample data shows the availability of different car types with a car dealer in their different showrooms.

I want an Hlookup formula that can not only search across the first row (which is the standard) for the key “SUV” but also the second row for “Showroom 2”.

multiple criteria lookup in Hlookup - Google Sheets

Then I want to return the value from the third row and that would be the value from cell G6 as per the above range. See that Hlookup tips.

Multiple Conditions in Hlookup in Google Sheets – Formula Example

By default, the Hlookup is only capable of searching across the first row. In any case, we can’t change that! So what we can do is combine the first and second row and then combine the search keys/conditions too.

You May Also Like: Lookup, Vlookup, and Hlookup Differences in Google Sheets

I know you want more explanation. First, see the formula then you can get my formula explanation.

=ArrayFormula(hlookup("SUVShowroom 2",{A1:G1&A2:G2;A3:G3},2,0))

In the above example formula, I have used multiple conditions in Hlookup. How?

The multiple conditions are;

In Row 1: SUV

In Row 2: Showroom 2

Now see this Hlookup Syntax:

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

The argument search_key is the so-called condition/criterion. See, you can only use one search_key/condition in Hlookup. So combine our two conditions into one.

So the condition would be “SUVShowroom 2”. Here you can use cell references too. If condition 1 is in cell G7 and condition 2 is in cell G8, use them as below.

G7&G8

Check the Hlookup syntax and you can see that the second argument is the “range”. Since we have combined the search keys, combine the relevant rows containing the search_keys and use it in the “range” in Hlookup.

{A1:G1&A2:G2;A3:G3}

In my original sample data (see the screenshot above) there are three rows. Now we have only two rows in the range due to the combining of the first and second rows.

So the row index (the row to look up) is 2, not 3. That’s all.

Follow these steps to use multiple conditions in Hlookup in Google Sheets. Any questions related to this, please feel free to ask me.

Conclusion:

You have learned a very useful Hlookup tip in this Google Sheets tutorial. I know some of you may want to dig deep into these multiple conditions uses.

I mean you may be interested to know how to use Hlookup when the conditions are from a different row other than the first row.

Here is the solution. In the range in Hlookup, reorder the row positions using Curly Braces. And use the row index number intelligently.

For example, my data range is A1:G3. If I want Hlookup to search across the second row, I can reorder the range as below.

{A2:G2;A1:G1;A3:G3}

Hope you have enjoyed the stay. See you back again with another awesome Spreadsheet tutorial.

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.

Highlight Upcoming Birthdays in Google Sheets

When highlighting upcoming birthdays in Google Sheets, one important aspect is ignoring the year...

Compare Two Tables for Differences in Excel

To compare two tables with similar data for differences, you can use the XLOOKUP...

Calculate Weighted Average in Pivot Table in Google Sheets

You can calculate a weighted average in a Pivot Table using a custom formula...

Summarize Data and Keep the Last Record in Google Sheets

In Google Sheets, we can summarize data (like sum, average, min, max, count) using...

More like this

Highlight Upcoming Birthdays in Google Sheets

When highlighting upcoming birthdays in Google Sheets, one important aspect is ignoring the year...

Calculate Weighted Average in Pivot Table in Google Sheets

You can calculate a weighted average in a Pivot Table using a custom formula...

Summarize Data and Keep the Last Record in Google Sheets

In Google Sheets, we can summarize data (like sum, average, min, max, count) using...

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.