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