Wildcards are special symbols used for fuzzy search in Excel, mainly including question marks (?) and asterisks (). The former represents any character and the latter represents any multiple characters. When using VLOOKUP, you can combine these wildcards to achieve partial matching in the search value, such as entering "AC" to match "AC", "ABC", etc.; the formula structure is =VLOOKUP ("Find Value &", table area, column number, FALSE). Be careful to use the FALSE parameter to enable fuzzy search in exact matching mode and ensure that the search value is in text format. In addition, VLOOKUP only returns the first match, and if the search value is a number or cell reference, it must be converted to text format first, for example =VLOOKUP(D1 & "", A:B, 2, FALSE).

When using VLOOKUP function in Excel, if the search value does not exactly match the data in the table, you can use wildcard characters to achieve fuzzy search. This approach is especially suitable for handling partially matched or slightly different formatted data.

What are wildcards? What are the commonly used ones?

Excel supports two main wildcard characters:

• Question mark ? represents any character
• * Asterisk`` **: Represents any multiple characters

For example:

• "A?C" can match "ABC" and "A2C" etc.
• "A*C" can match "AC" , "ABC" , "ABBC" , etc.

These wildcards are often used in text searches and are especially suitable for finding content containing uncertain characters.

How to use wildcards in VLOOKUP?

When you are not sure about finding the full value, you can add wildcards to the first parameter (find value) of VLOOKUP to achieve fuzzy matching.

Basic syntax:

 =VLOOKUP("Find Value &*", Table Area, Column Number, FALSE)

A few points to note:

• Use FALSE to enable wildcard search in exact match mode
• The search value must be spliced with * , such as "A*"
• The search value must be of text type, not number

Sample Scenario:

Suppose you have an employee information table, column A is the employee number (such as EMP1001 , EMP1002 ), and column B is the name. You want to find all employees with prefixed EMP10 by typing "EMP10".

The formula can be written like this:

 =VLOOKUP("EMP10*", A:B, 2, FALSE)

This formula will return the first match, such as the name corresponding to EMP1001 .

Note: VLOOKUP will only return the first match result when using wildcards. If there are multiple matching items, the following will not be listed.

Frequently Asked Questions and Notes

• Why can't the results be found?

  • Make sure to use the FALSE parameter
  • Is the search value spliced with wildcard characters?
  • Is the data sorted? No sorting is required, because fuzzy search is performed in exact matching mode

• Only used in text fields

  • If the search value is a number or cell reference, it needs to be converted to text format first

• How to use cell content as search keywords?

  • Assuming the search word is in cell D1, you can write it like this:

     =VLOOKUP(D1 & "*", A:B, 2, FALSE)

    ---

    Let's summarize

    Using wildcards allows VLOOKUP to deal with incomplete matching situations more flexibly, especially in the face of search tasks with some known information. Just remember a few key points:

    • Use * or ? to construct fuzzy search criteria
    • Must be used with FALSE
    • Returns the first match

    Basically all is it, not complicated but it is easy to ignore details.

    The above is the detailed content of excel vlookup with wildcard characters. For more information, please follow other related articles on the PHP Chinese website!