Excel users might be familiar with VLOOKUP function as a useful tool to look up for value is needed. Besides, Excel also provides HLOOKUP function to look up for text value across a range in a column.
HLOOKUP has almost the exact the same syntax, structure expect that HLOOKUP is occurring horizontally. HLOOKUP function can be used to search and retrieve data from a specific row in a table. This post will demonstrate how to use HLOOKUP combine with an asterisk (*) wildcard to find the first text value in each row of a table.
Get first text value with HLOOKUP
Considering the example below, the formula in G2 is:
Figure 1 – Get first text value with HLOOKUP
Let’s break formula down into parts:
The lookup value is “*”. This wildcard matches one or more text values.
Range C2:F2 is the table array from which to retrieve data. This array changes as the formula is extended down column G.
The row index is 1 because the range contains one row.
The range lookup is set 0, which is FALSE, to force an exact match. This is required when using wildcards with HLOOKUP.
This formula finds and returns the first text value found from column C to column F in each row.
- This formula just works with text value, not numeric values, because the asterisk wildcard matches the only text. So if a cell contains numeric values, the formula will show the next cell that contains text values. You can use INDEX and MATCH function to match numeric values.