Get first text value in a row

The goal is to return the first text value in each row, ignoring both blank cells and cells that contain numbers. This problem can be solved using the HLOOKUP function. In newer versions of Excel, you can use the XLOOKUP function, which is a more modern lookup function that can look up values in vertical or horizontal ranges.

Wildcards in Excel formulas

Some Excel functions support wildcards, which can be used to solve this problem. In this case, we can use the asterisk (*) as a wildcard, which will match any number of text characters. The asterisk (*) wildcard will ignore empty cells, numbers, and errors.

 HLOOKUP function

The HLOOKUP function is an older function in Excel widely used for common lookup problems that involve horizontal ranges. Like VLOOKUP, HLOOKUP supports wildcards when operating in exact match mode. The generic syntax for HLOOKUP looks like this:

=HLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Where each argument has the following meaning:

• lookup_value - the value to look for
• table_array - the horizontal range to look in
• row_index_num - the number of the row to retrieve table_array
• range_lookup - settings for exact and approximate matching (must be exact for wildcards)

For more details, see How to use the HLOOKUP function.

In the worksheet shown, we can use HLOOKUP to retrieve the first text value in C5:H5 like this:

=HLOOKUP("*",C5:F5,1,0)

The values provided to VLOOKUP are as follows:

• lookup_value - "*" (the wildcard)
• table_array - C5:F5 (one-row table)
• row_index_num - 1 (first row)
• range_lookup - 0 or FALSE for an exact match (required for wildcards)

In this configuration, HLOOKUP ignores empty cells and numeric values and returns "A", which is the first text value in C5:F5. Note that HLOOKUP is limited to horizontal ranges only. To solve this problem in the same way with a vertical range you can use VLOOKUP or XLOOKUP, as explained here. 

XLOOKUP function

The XLOOKUP function is a modern function that can replace both the VLOOKUP function and the HLOOKUP function. Like HLOOKUP and VLOOKUP,  XLOOKUP supports wildcards, and generic syntax for required inputs in this problem is:

=XLOOKUP(lookup_value,lookup_array,return_array,,match_mode)

Where each argument has the following meaning:

• lookup_value - the value to look for
• lookup_array - the range or array to search within
• return_array - the range or array to return values from
• if_not-found - value to return if no match is found (omitted above)
• match_mode - settings for exact, approximate, and wildcard matching

For more details, see How to use the XLOOKUP function.

To solve this problem with XLOOKUP, we can use a formula like this:

=XLOOKUP("*",C5:F5,C5:F5,,2)

Here are the values provided to XLOOKUP:

• lookup_value - "*" (the wildcard)
• lookup_array - C5:F5
• return_array - C5:F5
• if_not-found - omitted
• match_mode - 2 (to enable wildcard matching)

In this configuration, XLOOKUP ignores the empty cell and zero values in the first three cells and returns "A", which is the first text value in C5:F5. One of XLOOKUP's hallmark features is flexibility. For example, to retrieve the month in which the first text value occurs, we use a formula like this:

=XLOOKUP("*",C5:F5,$C$4:$F$4,,2) // get month

This formula will return the corresponding month from the range C4:F4 that aligns with the first text value in each row, as seen below:

Array formula approach

A more modern and flexible way to solve this problem with XLOOKUP is with an array formula that uses the ISTEXT function. With XLOOKUP, you can use a formula like this:

=XLOOKUP(TRUE,ISTEXT(C5:F5),C5:F5)

In a nutshell, we use the ISTEXT function to test the values in C5:F5 and return an array of TRUE and FALSE values. We then configure XLOOKUP to search this array for the first TRUE value and return the corresponding value from C5:F5. This is a more flexible approach because it can be easily adapted to test for other types of content, like numbers, errors, and even blank cells. For a more complete explanation, see this example.