How to Lookup Text in Excel (7 Suitable Methods)

Useful and faster methods for looking up text value in Excel are necessary especially when you have a larger dataset. In this article, I’ll discuss lookup text in Excel using 7 suitable methods with relevant examples and also with the explanation. So, you may adjust the method for your dataset.


Download Practice Workbook


7 Methods to Lookup and Extract Text in Excel

Before moving into the methods, let’s have a look at our today’s dataset where employee names are provided with their Employee ID No and Email ID.

Dataset


1. Applying the LOOKUP Function to Extract Text

Firstly, I’ll show the use of the LOOKUP function to retrieve a text value from a dataset.

The function returns the value in a single row or column from a similar position in the row or column.

For example, if the lookup value is 1003 (Employee ID) and you need to find the employee name for the ID, you may use the following formula.

=LOOKUP(F5,B5:B12,C5:C12) 

Here, F5 is the lookup value, B5:B12 is the lookup vector (cell range) of Employee ID, and C5:C12 is the result vector (cell range) of Employee Name.

Lookup Text Using LOOKUP Function

Eventually, you’ll get the following output where the names of employees are available based on the ID.

Lookup Text Using LOOKUP FunctionRead More: How to Lookup Multiple Values in Excel (10 Ways)


2. Lookup Text Using the VLOOKUP Function 

The VLOOKUP function is one of the popular functions for finding value in a column (vertical lookup).

In the following example, the function is used to extract the text for a certain cell.

The adjusted formula with wildcards for finding text value is-

=VLOOKUP("*"&F5&"*",B5:D12,1,FALSE)

Here, F5 is the lookup text, B5:D12 is the table array (dataset from which to retrieve the text), 1 is the column index number, and lastly FALSE is the exact matching.

Lookup Text Using the VLOOKUP Function

Visit the Lookup Text Using VLOOKUP in Excel article to know more uses of the formula to extract text from a dataset.

Read More: How to Lookup a Table in Excel (8 Methods)


3. Using the Combination of VALUE & VLOOKUP Function to Find Text

The VALUE function returns the number from a looking text value, we can utilize the function along with the VLOOKUP function to find the text.

The combined formula will be like the following-

=VLOOKUP(VALUE(F5),B5:C12,2,FALSE)

Here, F5 is the lookup text, B5:D12 is the table array (dataset from which to retrieve the text), 2 is the column index number, and lastly FALSE is for the exact matching.

More importantly, VALUE(F5) returns the number and then the VLOOKUP extracts the text value.

Using The Combination of VALUE & VLOOKUP

Read More: Excel LOOKUP vs VLOOKUP: With 3 Examples


4. Utilizing HLOOKUP to Get the Text Value

The HLOOKUP function returns a value based on the lookup value in a row (horizontal lookup).

We can utilize the function to get the text value sequentially using Asterisk (*) at the beginning of the formula.

So, the formula will be-

=HLOOKUP("*",B5:D12,1,0)

Here, B5:D12 is the table array, 1 is the row index number, and 0 (FALSE) is for exact matching.

Using HLOOKUP Function to return text

Read More: 7 Types of Lookup You Can Use in Excel


5. Applying XLOOKUP with the EXACT Function

The XLOOKUP, an amazing lookup function introduced in Excel 365, extracts value in a range or array.

In the following example, the function along with the EXACT function is used to retrieve a value.

The combined formula is-

=XLOOKUP(TRUE,EXACT(F5,B5:B12),C5:C12) 

Here, F5 is the lookup value, B5:B12 is the lookup array that contains the lookup value and C5:C12 is the return cell range as we want to find the name.

Lookup Text Applying The XLOOKUP & EXACT Function

After entering the formula, the output looks as follows.

Lookup Text Applying The XLOOKUP & EXACT Function


6. Lookup Text Using XLOOKUP with Single Criteria

Assuming that the employee name is divided into separate columns (one is the first name and the other one is the last name).

Now, we’ll find the email of the first name easily with the XLOOKUP function.

The adjusted formula is-

= XLOOKUP(F5,B5:B12, D5:D12, 0, -1 ) 

Here, F5 is the lookup value, B5:B12 is the lookup array that contains the lookup value, C5:C12 is the return cell range as we want to find the name, 0 is for if the value is not found, and -1 refers to match mode.

Lookup Text Using The XLOOKUP with Single Criteria

The output will be as follows.

Lookup Text Using The XLOOKUP with Single Criteria


7. Lookup Text Using XLOOKUP for Multiple Criteria

Lastly, we’ll extract the text value that matches multiple criteria using the flexible XLOOKUP function.

For example, if you want to find the email based on the First Name and Last Name, you may use the following formula.

=XLOOKUP(B15&C15,B5:B12&C5:C12,D5:D12,0,-1)

Here, B15 is the first name and C15 is the last name, B5:B12 is the lookup array (cell range for the First Name), C5:C12 is another lookup array (cell range for the Last Name), 0 is for if the value is not found, and -1 is the match mode.

Using The XLOOKUP for Multiple Criteria

After inserting the above formula, you’ll get the output like the following picture.

Using The XLOOKUP for Multiple Criteria

Read More: How to Lookup with Multiple Criteria in Excel (Both AND or OR Type)


Conclusion

This is how you can find the lookup text using the formula in Excel. Really, I hope this article might be beneficial for you. Anyway, if you have any queries and suggestions, please don’t forget to share them in the following comments section.


Further Readings

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

2 Comments
  1. Hi

    Is possible to sum all WA11?

    (A1) WA11 4

    (A2) AdBlue 1, WA11 223

    (A3) AdBlue 3, WA11 32, shift 4

    … and everything is in one column.

    Thanks you very much for your help.

    Sincerely Marko

  2. Hi Marko
    I hope you are doing well.
    After going through your problem, I will give you 2 possible solutions.
    Solution 1:
    This will require you to insert the number after “WA11” within parentheses.
    ● Add the parentheses.

    1

    ● Then, write down the following formula in B2.
    =MID(A1,SEARCH("(",A1)+1, SEARCH(")",A1)-SEARCH("(",A1)-1)+0

    2

    ● Then, press ENTER. Excel will extract the number inside the parentheses.

    3

    ● Then, AutoFill up to B3.

    4

    ● Finally, add the numbers.

    5

    Solution 2:
    ● This will require you to have a helping column with the numbers with “WA11”. But this is a bit tedious if you have a lot of data.

    6

    ● Now, add the numbers.

    7

    Hope this helps. Thank you.

Leave a reply

ExcelDemy
Logo