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.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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.