Get first text value in a list in Excel

by

This tutorial shows how to Get first text value in a list in Excel using the example below;

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.

Worked Example:   Excel VLOOKUP and HLOOKUP functions Examples

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.

Worked Example:   Faster VLOOKUP with 2 VLOOKUPS in Excel

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

Worked Example:   Partial match with VLOOKUP in Excel

Leave a Reply

Your email address will not be published.