Use a search key to lookup header row and filter non-blanks in the found column. This is something like horizontal lookup and filtering the rows in the found column. Don’t confuse it with;

1. Two-way lookup – In this lookup, search a key vertically and return a value from a horizontal lookup column.

2. Two-way filter – Filter Rows and Columns Conditionally.

So here things are quite different. See this example to understand how to lookup header row and filter non-blanks in the found column in Google Sheets.

Example:

In cell H2 I have the search key to search the header row B2: F2. For convenience to switch the search key easily, I have used a data validation drop-down in that cell. It’s not necessary though.

Must Read: The Best Data Validation Examples in Google Sheets.

The formula in cell I3 searches the keyword in cell H2 across the row B2: F2. Once found, the formula filters that column for the values that are greater than 0, that means non-blanks.

In the output, I have included the columns that contain the provided search key + the first column. But you can include all the columns or any number of specific columns in your output. I’ll explain that also in this post.

The Formula to Lookup Header and Filter Non-Blanks in That Column

I am going to combine the Google Sheets Query and Match functions for this.

The role of the Match in the following formula is to find the column number that contains the search key.

The role of the Query is no doubt the filtering of the source data based on the column number that returned by the Match.

Formula:

=Query({B2:F},"Select Col1,Col"&match(H2,$B$2:$F$2,0)&" where Col"&match(H2,$B$2:$F$2,0)&">0")

Query + Match Combo Formula Explanation:

Let’s see how this formula searches across the header row and then filters non-blanks in the found column in Google Sheets. It’s very simple to understand.

Actually, you can simply read the above formula as below. Assume the search key in cell H2 is “Mar”

=query({B2:F},"Select Col1,Col4 where Col4>0")

This is what happens in the above formula. The #4 in Col4 is returned by the Match.

The Match formula horizontally matches the search key (H2) in the header row of the data range and returns the column number. That column number we can use in Query to filter the dataset. That’s what I have done.

You have learned how to lookup header and filter non-blanks in Google Sheets. Instead of non-blanks, you can use other criteria.

You May Like: How to Skip Blank Cells in Vlookup in Google Sheets.

For example, I want to Lookup one search key in the header as above. Then what I want is to filter the values in the found column that is greater than or equal to 50.

My search key this time in cell H2 is “Jan”

=Query({B2:F},"Select Col1,Col"&match(H2,$B$2:$F$2,0)&" where Col"&match(H2,$B$2:$F$2,0)&">=50")

You will get the below output.

Item Jan
Peer 50

Similarly, you can use any other criteria in the Query. It depends on your Query skill. If you are not much familiar in using Query, please do check my Query tutorial in my Google Sheets Functions Guide.

Note: In order to learn the formulas in this tutorial, please prepare the sample datasets in your Google Sheets and apply the formulas. Don’t forget to change the Comma to Semicolon depending your Locale Setting.

Must Read: How to Change a Non-Regional Google Sheets Formula.

Lookup Header and Filter Non-Blanks in that Column and Return all the Columns

What you have achieved is the skill to horizontally lookup and filter the dataset. Now you can learn how to control the columns in the output.

=Query({B2:F},"Select * where Col"&match(H2,$B$2:$F$2,0)&">=50")

This formula would search across the header row for the keyword in cell H2 and filter the column that contains the keyword as the header. This formula would then output the entire columns.

Horizontal Lookup and Filter – Output Specific Columns

Here is another scenario.

=Query({B2:F},"Select Col1 where Col"&match(H2,$B$2:$F$2,0)&">=50")

This formula would return only the first column that contains the item name.

Real-Life Use of Lookup Header and Filter Non-Blanks in Google Sheets

Here is a real-life example to the use of lookup header and filter matching column. I am going to prepare a Sales Invoice in Google Sheets in that making use of the above formula.

In this Sales Invoice, I have used two formulas that in cell H8 and I8 (please refer the screenshot below).

The Formula in cell H8:

=Query({A2:E},"Select Col1 where Col"&match(K1,$A$2:$E$2,0)&">0 label Col1''")

The above Query+Match combo formula returns the first column after filtering the horizontal lookup column. That means it returns only the item names for the existing order quantity in the lookup column (order #)

The Formula in cell I8:

=Query({A2:E},"Select Col"&match(K1,$A$2:$E$2,0)&" where Col"&match(K1,$A$2:$E$2,0)&">0"&" Label Col"&match(K1,$A$2:$E$2,0)&"''",1)

The above Query+Match formula returns the horizontal lookup column. That means the formula returns only the quantities.

Both the above formulas are in line with the key formula that you have learned in this tutorial. The only difference is in the filter column. All the Match formula used in the second formula is the same which returns the horizontal lookup column number.

That’s all. Enjoy!

Similar Topics:

1. Two-way Lookup and Return Multiple Columns in Google Sheets.

2. How to Find Max Value in Each Row in Google Sheets [Array Formula].