Explanation

The general goal is to return the first numeric value in a row or column. More specifically, in the worksheet shown, we have dates in column B and a numeric value in the range C5:C16. Notice that all of the cells in this range have numeric values. Some are blank and some contain text values. We want the first number that appears in the range C5:C16. This problem can be solved using the XLOOKUP function or, in older versions of Excel, an INDEX and MATCH formula. Both methods are explained below.

XLOOKUP function

The XLOOKUP function is a modern upgrade to the VLOOKUP function. XLOOKUP is flexible and can handle many different lookup scenarios. The generic syntax for required inputs looks like this:

=XLOOKUP(lookup_value,lookup_array,return_array)

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

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

The ISNUMBER function

The ISNUMBER function returns TRUE when a cell contains a number, and FALSE if a cell is empty or contains a text value. If A1 contains “Age”, A2 contains 32, and cell A3 is empty, the ISNUMBER function returns the following:

=ISNUMBER(A1) // returns FALSE
=ISNUMBER(A2) // returns TRUE
=ISNUMBER(A3) // returns FALSE

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

XLOOKUP formula

In the worksheet shown, the formula in cell E5 combined XLOOKUP and ISNUMBER like this:

=XLOOKUP(TRUE,ISNUMBER(C5:C16),C5:C16)

At a high level, the XLOOKUP function is configured with the lookup_value set to TRUE. The lookup_array is generated with the ISNUMBER function here:

ISNUMBER(C5:C16)

Because the range C5:C16 contains 12 cells, ISNUMBER returns an array that contains 12 TRUE and FALSE results like this:

{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}

The TRUE values in this array indicate cells that contain numbers. The FALSE values indicate cells that either contain text values or are empty, such as cell C16. This array is then returned directly to the XLOOKUP function as the lookup_array . At this point, we have the following:

=XLOOKUP(TRUE,{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE},C5:C16)

With a lookup value of TRUE, XLOOKUP matches the first TRUE in the lookup_array (the fourth value), and returns the corresponding value from the range C5:C16 (10) as a final result.

Testing the formula

This formula is dynamic and will always return the first numeric value. To test the formula, we can add the number 12 to cell C7. Now the formula returns the 12, since 12 becomes the first numeric value in the range C5:C16.

The result of typing the number 12 into cell C7 - 1

INDEX and MATCH formula

In older versions of Excel that do not provide the XLOOKUP function, you can use an array formula based on INDEX and MATCH like this:

=INDEX(C5:C16,MATCH(TRUE,ISNUMBER(C5:C16),0))

Note: this is an array formula and must be entered with control + shift + enter in Excel 2019 and older.

This formula uses the same logic as the XLOOKUP formula above. The MATCH function is used to find the position of the first numeric value in C5:C16.

MATCH(TRUE,ISNUMBER(C5:C16),0)

The ISNUMBER function returns an array of TRUE (numeric) and FALSE (not numeric) values:

{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}

The array is returned to the MATCH function as the lookup_array . The lookup_value is given as TRUE and match_type is set to 0 to require an exact match:

MATCH(TRUE,{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE},0)

MATCH then returns the location of the first TRUE in the array (4) as the row number to INDEX:

=INDEX(C5:C16,4) // returns 10

INDEX then returns the value from the fourth cell in C5:C16 (10) as a final result.

For more details, see How to use INDEX and MATCH .

Explanation

The general goal is to return the first text value in a range. Specifically, we have dates in column B and some city names in column C. We want a formula to find the first city listed in the range C5:C16. Because some cells in C5:C16 are empty, and some contain zeros, we need to ignore these cells in the process. This problem can be solved using the XLOOKUP function. In older versions of Excel, you can use the VLOOKUP function or an INDEX and MATCH formula. It is also possible to solve this problem with a more modern array formula based on the ISTEXT function. See below for details

Wildcards in Excel formulas

Some Excel functions support wildcards , which can be used to solve this problem. In this case, the wildcard we want is the asterisk () which will match any one or more characters. It’s not obvious from the description, but the asterisk () wildcard will only match text characters . It will ignore empty cells, numbers, and errors.

XLOOKUP function

The XLOOKUP function , a modern upgrade of the VLOOKUP function, offers one solution to this problem. When XLOOKUP is used with wildcards, the generic syntax for required inputs looks like this:

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

In the worksheet shown, the formula in E5 is:

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

Here are the values provided to XLOOKUP:

  • lookup_value - “*” (the wildcard)
  • lookup_array - C5:C16
  • return_array - C5:C16
  • 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 “New York”, which is the first text value in C5:C16. Although the range in this example is organized vertically, XLOOKUP can be used in the same way with a horizontal range. Also, we can easily adapt this formula to return the date (as seen in F5) like this:

=XLOOKUP("*",C5:C16,B5:B16,,2) // get date

This formula will return the corresponding date from the range B5:B16 that aligns with the first text value in C5:C16 - in this case, 04-Aug-23.

VLOOKUP function

The VLOOKUP function is an older function in Excel widely used for common lookup problems that involve vertical ranges. Like XLOOKUP, VLOOKUP supports wildcards. The generic syntax for VLOOKUP looks like this:

=VLOOKUP(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 table to look within
  • col_index_num - the number of the column to retrieve
  • range_lookup - settings for exact and approximate matching (must be exact for wildcards)

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

In the worksheet shown, you can use VLOOKUP to retrieve the first City in the range C5:C16 like this:

=VLOOKUP("*",C5:C16,1,0)

The values provided to VLOOKUP are as follows:

  • lookup_value - “*” (the wildcard)
  • table_array - C5:C16 (one-column table)
  • col_index_num - 1 (first column)
  • range_lookup - 0 or FALSE for an exact match (required for wildcards)

In this configuration, VLOOKUP ignores the empty cell and zero values in the first three cells and returns “New York”, which is the first text value in C5:C16. Note that VLOOKUP is limited to vertical ranges only . To solve this problem in the same way with a horizontal range you can use the HLOOKUP function . Alternatively, you can use an INDEX and MATCH formula, as explained below.

INDEX and MATCH

One problem with VLOOKUP in this problem is that we can’t return the date in B5:B16 that corresponds to the first text value in C5:C16. This is one of the limitations of VLOOKUP , which requires the lookup values to be the first column in a table . However, we can use INDEX and MATCH to get both the city and the date like this:

=INDEX(C5:C16,MATCH("*",C5:C16,0)) // get city
=INDEX(B5:B16,MATCH("*",C5:C16,0)) // get date

The approach is the same as with XLOOKUP and VLOOKUP above - we are using MATCH with the (*) wildcard to find the first text value. The location is then provided to INDEX, which returns the final result.

For more details on INDEX with MATCH, see: How to use INDEX and MATCH .

Array formula approach

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

=XLOOKUP(TRUE,ISTEXT(C5:C16),C5:C16,,2)

In a nutshell, we use the ISTEXT function to test the values in C5:C16 and return an array of TRUE and FALSE values. We then configure XLOOKUP to search this array for the first TRUE value. 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 .