Excel formula: Get first text value in a list

Generic formula

=VLOOKUP("*",range,1,FALSE)

Summary

If you need to get the first text value in a list (a one-column range) you can use the VLOOKUP function set to exact match, with a wildcard character for the lookup value.

In the example the formula in D7 is:

=VLOOKUP("*",B5:B11,1,FALSE)

Explanation

The asterisk (*) wildcard character matches any text value. When used with VLOOKUP in exact match mode (the 4th argument, range_lookup, set to FALSE), the asterisk will match the first cell that contains text. Since we are supplying the column as 1, we get the value from the same range we are using for the table.

Note: this only works for text values, not numbers.

Author

Dave Bruns

Related formulas

Get first non-blank value in a list

So, the gist of the problem is this: We want to get the first non-blank cell, but we don't have a direct way to do that in Excel. We could use VLOOKUP with a wildcard * (see link below), but that will only work for text, not numbers. So, we need to...

Two-way lookup with VLOOKUP

This is a standard VLOOKUP exact match formula with one exception: the column index is supplied by the MATCH function. Note that the lookup array given to MATCH (B2:E2) representing column headers deliberately includes the empty cell B2. This is...

Calculate grades with VLOOKUP

In this example, the scores in column F are lookup values for the VLOOKUP function. The lookup table is the named range "key" (B5:C9). The column index is provided as 2, since we want VLOOKUP to return a grade from the second column. Finally, the...

Get employee information with VLOOKUP

In this case "id" is a named range = B4 (which contains the lookup value), and "data" is a named range = B8:E107 (the data in the table). The number 3 indicates the 3rd column in the table (last name) and FALSE is supplied to force an exact match...

Merge tables with VLOOKUP

This is a standard "exact match" VLOOKUP formula with one exception: the column index is calculated using the COLUMN function. When the COLUMN function is used without any arguments, it returns a number that corresponds to the current column. In...

VLOOKUP without #N/A error

When VLOOKUP can't find a value in a lookup table, it returns the #N/A error. The IFERROR function allows you to catch errors and return your own custom value when there is an error. If VLOOKUP returns a value normally, there is no error and the...

Get nth match with INDEX / MATCH

At the core, this formula is simply an INDEX formula that retrieves the value in an array at a given position. The value for n is supplied in column H, and all the "heavy" work that the formula does is to figure out the row from which to retrieve a...

Related functions

Excel VLOOKUP Function

VLOOKUP is an Excel function to look up data in a table organized vertically. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. Lookup values must appear in the first...

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

500 Formulas | 101 Functions

Exceljet

Quick, clean, and to the point

Get first text value in a list

Excel Formula Training

Download 200+ Excel Shortcuts

Excel video training