You can code ‘one of the best formulas’ to search a cell value and return its header using Hlookup. Use an Hlookup formula to search an entire table and find the header, I mean to retrieve the column name or field label, in Google Sheets.
One of the best formulas?
Yes! Because I have similar tutorial on this page. I will surely link to that pages later on this post.
I know Hlookup is for searching across the first row. But you can use the Hlookup to search across any row with a workaround. I am going to share that awesome workaround here in this post.
This GIF gives you a pretty clear idea about searching an entire table and finding the header in Google Sheets, right?
If you are my regular reader or returning visitor, you might have seen similar tutorials on this page for a similar as well as different purposes.
Before further proceeding, let me introduce you the earlier similar looking tutorials.
- Search Across Columns and Return the Header in Google Sheets – In this earlier tutorial, I have detailed the above solution but using a very different approach. There I have used the function Query as the key formula. Not so difficult to code, but of course, a little complex.
- Lookup and Retrieve the Column Header in Google Sheets – This is a slightly different solution. This you can use to search down the first column then return the header from the required columns.
This time I am using Hlookup which is more elegant and very simple to code.
Formula to Search Entire Table and Find the Header in Sheets
As you may know, Hlookup can only search across the first row in a table (range/array) and return value from the column found. But the reverse is also possible! I mean you can search an entire table (array/range) and return the header.
Let’s code the formula for the above same sample data. The table is in the range B2:E6. But we can use an infinite table range in the formula to include future rows. So I am going to use the range B2:E in the formula.
Hlookup First Row Vs. Hlookup Any Row
The default behavior of Hlookup is to search across the first row as below.
You can search the keyword “North”, “South”, “East” or “West” in Hlookup as it’s in the first row of the table or range.
Assume you use “South” as the keyword. Then you can return any value in the range C3:C.
Example:
=hlookup("South",B2:E,2,0)This would return the value “Adam” because I have specified 2 as the row index (second row in the range).
I want to do the reverse of this. I mean search the keyword “Adam” and return the header “South”.
Below you can find the Hlookup formula to search an entire table and find the header in Google Sheets.
Hlookup Formula to Search Entire Table and Find the Header Row Value
Formula:
=hlookup(1,{SORTN(search(F3,B3:E));B2:E2},2,0)
You can use this same Hlookup formula to find the value in the header row of any cell in Google Sheets.
For that, I mean for your source data, you just need to change the table reference in the formula that is within the Curly Brackets.
You must learn to code this formula then only you can use it in your source data. I am going to elaborate that on the formula explanation part below.
Formula Explanation – How Hlookup Searches Entire Table and Finds the Header
In the above Hlookup formula you only need to change the ‘range’. Understanding Hlookup arguments are a must to follow my formula. So let me start with the syntax.
Syntax:
HLOOKUP(search_key, range, index, [is_sorted])As per my formula, the search_key is 1, range is {SORTN(search(F3,B3:E));B2:E2}, index is 2 and is_sorted is 0.
In this, you only need to understand the range because that is the only part of the formula that you must change for your range. Other values in the formula are constant.
Virtual Hlookup Range Using the Search Function to Search Across Any Row
See the value in cell F3. That cell contains the search_key which is “Adam”.
In the Hlookup range above you can see one formula based on the Search function. I have used the cell F3 (I mean the search key “Adam”) in that.
=search(F3,B3:E)If you enter this formula in your Sheet, it won’t work. Because it needs array support. You must either use Filter, Sort, Sortn, Index or ArrayFormula function with it.
New to these array functions? Later, you can feel free to go thru’ my Functions Guide.
Just enter the above formula as below in your Sheet and see the output.
=ArrayFormula(search(F3,B3:E6))
In the master formula, I have this formula within SORTN. So there I didn’t use the ArrayFormula. Later I will tell you why I have opted the SORTN over the ArrayFormula.
The number 1 in the formula indicates that the first cell in the second column contains the search key “Adam”.
Change the search key “Adam” to “Roger” in cell F3 and see what happens.
This means that the search key “Roger” is in the third row in the fourth column.
You must understand that Hlookup can only search across the first row. It can never search across second, third or any other row.
So the above virtual range doesn’t make any sense, right? But it does make sense when you use it in SORTN! Please read on to get more insight on this usage.
Fine Tune Hlookup Virtual Range with SORTN
Simply replace the ArrayFormula in the Search formula with SORTN. It would then return only one row that contains the number 1.
Actually, the SORTN sorts the virtual table (the output of Search) and limits the output to one row.
Due to the sorting involved, the row contains the number # 1 will always be on the top.
=SORTN(search(F3,B3:E6))I have joined the header row B2:E2 to it as the second row and that is the Hlookup (virtual) range.
Remember the search key is 1 in Hlookup. It searches across the first row in this virtual range and returns the header row (now second column).
Follow this method to code an Hlookup formula that searches an entire table and finds the header in Google Sheets.
Thanks for the stay, enjoy!
