Excel formula: Get first text value in a list

Generic formula

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

Explanation

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)

How this formula works

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

If you need to get the first non-blank value (text or number) in a in a one-column range you can use an array formula based on the INDEX, MATCH, and ISBLANK functions. In the example the formula we're using is: { = INDEX ( B3:B11 , MATCH ( FALSE ,...

Two-way lookup with VLOOKUP

Preface Inside the VLOOKUP function, the column index argument is normally hard-coded as a static number. However, you can also create a dynamic column index by using the MATCH function to locate the right column. This technique allows you to create...

Calculate grades with VLOOKUP

If you want to calculate grades using the VLOOKUP function, it's easy to do. You just need to set up a small table that acts as the "key", with scores on the left, and grades on the right. This table must be sorted in ascending order, and VLOOKUP...

Get employee information with VLOOKUP

If you want to retrieve employee information from a table, and the table contains a unique id to the left of the information you want to retrieve, you can easily do so with the VLOOKUP function. In the example shown, the VLOOKUP formula looks like...

Merge tables with VLOOKUP

To merge tables, you can use the VLOOKUP function to lookup and retrieve data from one table to the other. To use VLOOKUP this way, both tables must share a common id or key. This article explains how join tables using VLOOKUP and a calculated...

VLOOKUP without #N/A error

To hide the #N/A error that VLOOKUP throws when it can't find a value, you can use the IFERROR function to catch the error and return any value you like. How the formula works When VLOOKUP can't find a value in a lookup table, it returns the #N/A...

Get nth match with INDEX / MATCH

To retrieve multiple matching values from a set of data with a formula, you can use the IF and SMALL functions to figure out the row number of each match and feed that value back to INDEX. In the example shown, the formula in I7 is: { = INDEX ( amts...

Related functions

Excel VLOOKUP Function

VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. The "V" stands for "vertical". Lookup values must appear in the...

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.