Explanation
The general goal is to return the first numeric value in a row or column.
Notice that all of the cells in this range have numeric values.
Some are blank and some contain text values.
We want thefirst numberthat appears in the range C5:C16.
Both methods are explained below.
XLOOKUP function
TheXLOOKUP functionis a modern upgrade to the VLOOKUP function.
XLOOKUP is flexible and can handle many different lookup scenarios.
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 thelookup_array.
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.
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.
For more details, seeHow to use INDEX and MATCH.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
you’re free to use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
