LOOKUP the First Text from a List in Excel

The VLOOKUP function works fine with wildcard characters. We can use this to extract the first text value from a given list in excel. Here is the generic formula.

Generic First Text Retrieving Excel Formula

=VLOOKUP("*",lookup_table,col,0)

"*": This a wildcard operator that is used to search any text.

lookup_table: The table in which you want to do the lookup.

col: The column number in the table that you want to fetch the value from.

0: for Exact match.

Let's have an example so that we can see this excel formula in action.

Example: Retrieve first text value from a list in excel

Here we have a list of data that contains some numbers, texts, and blanks. We will extract the first text value from this list using the above generic formula.

Write this VLOOKUP formula in a cell to retrieve the first text value from the list.

=VLOOKUP("*",A2:A8,1,0)

Hit enter. This returns John-3.

How does it work?

It is simple. The VLOOKUP function works with wildcard operators. It means that if you have just "*", VLOOKUP will lookup for any text value.

Here, the lookup value is "*", the lookup value is A2: A8, and the column index is 1 with match type exact. This formula looks for any text in range A2:A8 and returns the first text value from the range.

Notes:

  • Even spaces are considered as text. Hence if you have a cell that contains space, this formula will return that cell. It will look like a blank.
  • If you have numbers formated as text, this function will not work on them. If they are text with apostrophe (') then they will be caught.

So yeah guys, this how you can retrieve first text value from a list in excel. If you have any doubt or specific query regarding this topic or any other Excel/2010/2013/2016/365/VBA related query, ask in the comments section below.

Related Articles:

Remove matching characters in Text | To remove matching characters we won't use REPLACE function. We will use the SUBSTITUTE function. This function automatically removes given text with another text.

Remove leading and trailing spaces from text in Excel | The trailing spaces disturb your data set and it is necessary to remove any trailing or leading space from text in excel. This formula removes all trailing spaces.

Remove unwanted characters in Excel | To remove unwanted characters we use the SUBSTITUTE function.

Remove Characters From Right | The LEFT function can be used to remove characters from RIGHT of the text. Actually this function focuses on retaining text on left.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets. 

COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.