To illustrate this with a practical example from work, for example, the company name of our raw data record is the full name
Now the search content is abbreviated, and the corresponding information needs to be matched, as follows:
Today we introduce 4 formula methods to solve it quickly, 2 formulas are unlimited versions, and 2 formulas are formulas that need the latest version
Method 1: VLOOKUP formula
Because it is the full name of the match, we need to carry the pass character to find the match
The * sign indicates any number of characters
So the formula we use is:
=VLOOKUP("*"&D2&"*",A:B,2,0)
An asterisk is connected to both sides of the lookup value, which means: *Huawei*
It shows that as long as the text contains the Huawei character, it can be matched correctly, so it can be easily matched:
Method 2: Use the LOOKUP formula
There is a common use of LOOKUP when it comes to finding a match exactly
=LOOKUP(1,0/(Lookup Column = Lookup Value), Result Column)
The search value here is an abbreviation, so we need to change it to the FIND function formula, and the formula used is:
=LOOKUP(1,0/FIND(D2,A:A),B:B)
You can use the FIND function to determine which rows meet the conditions and return the results of column B
Method 3: Use the XLOOKUP formula
The new version of Excel has the XLOOKUP formula, and its usage is:
=XLOOKUP(Find Value, Find Column, Result Column, Not Found, Match By, Search By)
It defaults to the search value without a wildcard lookup, if the * sign is a wildcard, you need to go to the 5th parameter matching method to declare
So the formula we use is:
=XLOOKUP("*"&D2&"*",A:A,B:B,,2)
The 4th parameter is left unfilled, and the 5th parameter is entered 2 to indicate wildcard matching
Method 4: Use the FILTER formula
The FILTER formula is used for filtering, and the usage used for exact search is:
=FILTER(RESULT COLUMN, LOOKUP VALUE=LOOKUP COLUMN)
If you want to match the full name by abbreviation, you need to match the SEARCH function formula
The formula used is:
=FILTER(B:B,IFERROR(SEARCH(D2,A:A),0))
THROUGH THE SEARCH FORMULA, SEARCH IN COLUMN A, AND IF IT CAN BE SEARCHED, THE RESULT OF COLUMN B WILL BE RETURNED
There are 4 ways to match the full name with the full name, which can be summarized as follows:
Have you learned? Try it out!