[Organization and Sharing] 4 ways to use VLOOKUP and LOOKUP functions for fuzzy search

Most of the time we need to perform precise searches, but there are also times when fuzzy searches are needed. For example, searching for the full name based on the abbreviation, or classifying the name based on numerical values, etc. Fuzzy search is not the same as a blind man trying to find an elephant. Here are 4 ways to use VLOOKUP and LOOKUP functions to perform fuzzy search.

Today I will share with you several methods of fuzzy search.

Conventional fuzzy search is divided into two situations, one is numerical value; the other is text.

1. Numerical fuzzy search

First we share about fuzzy search of numerical values.

Example:

A company needs to customize work clothes for new employees. Now it needs to match the size of the clothes to the actual height of the employee.

#In this case, it is necessary to use fuzzy search to return the size corresponding to the height of each employee. There are two ways to do this.

Method 1: LOOKUP

Function formula: =LOOKUP(B2,{0;165;170;175;180; 185;190},{"S";"M";"L";"XL";"XXL";"XXXL";"XXXXL"})

Formula analysis:

This is to complete the fuzzy search in the form of LOOKUP vector. It can be understood as finding which interval the B2 cell is in {0;165;170;175;180;185;190}. If it is within a certain interval, the corresponding {"S";"M";"L";"XL will be returned. ";"XXL";"XXXL";"XXXXL"} text information.

For example, if 169 is between 165-170, then "M" text information will be returned.

The corresponding relationship between intervals here is as follows. 0 to less than 165 belongs to S size; 165 to less than 170 belongs to M size, and so on, until 190 or more belongs to XXXXL size.

If you don’t understand this, you can check the fourth section of the tutorial "Excel Function Learning: 5 Usage of LOOKUP Function" Routines for interval search”.

Method 2: VLOOKUP

Function formula: =IFERROR(VLOOKUP(B2 5,F:G,2,1) ,"S")

When we use the VLOOKUP function in daily work, the fourth parameter always enters 0, which means precise search. The fourth parameter here is 1, represents approximate search.

Formula analysis:

1. Use the function formula =VLOOKUP(B2,F:G,2,1) to return the size corresponding to the maximum value in the target area that is less than or equal to the lookup value . Note: Before using the VLOOKUP function for fuzzy search, the data in the search range F:G must be sorted in ascending order according to the search content (here, height).

For example, if we search for 172, then the maximum value less than or equal to 172 in the target area is returned, which is 170, and the corresponding size is M. Since the size of clothing is high rather than low, an employee with a height of 172 must customize L size clothes with a height of 175, so when we search for a match, we need to add 5 to the employee's height, so that the minimum size that is greater than the height can be returned. .

2. The height of some employees is still less than 165 even after adding 5, because 165 in column F is the smallest, so the required value cannot be found in column F for this part of data, and the VLOOKUP function returns an error value# N/A. We hope that all employees less than 165 will customize S numbers, so we use the IFERROR function to redirect the VLOOKUP error result to the text character "S".

2. Fuzzy search of text characters

The following is a fuzzy search of text. For example, by searching for AB, the value corresponding to the AAAABBB cell containing AB in the search area is returned. .

Example:

The following table shows the 2018 annual turnover data of each company, and the company name is the full name. Now we need to match the relevant turnover data based on the company abbreviation in another table.

Method 1: VLOOKUP wildcard

Function formula: =VLOOKUP("*" &E2&"*",A:B,2,0)

Formula explanation:

* represents all characters, "*"&E2&" *" means all content including the text content of cell E2.

Method 2: LOOKUP FIND

Function formula: =LOOKUP(1,0/FIND(E2,A$2:A$8), B$2:B$8)

Explanation of the formula: The

formula uses the LOOKUP search routine. Use the FIND function to determine the position of the text in cell E2 in cells A$2:A$8. If it exists, return a value greater than 0, otherwise return an error value; then 0/FIND(), you will get a set of 0 and error values array; finally, the LOOKUP function takes action, finds the largest value not greater than 1, 0, in the array, and returns the corresponding value in B$2:B$8 based on the position of 0.

By the way: If you only want to find the full name through the abbreviation, the formula can be changed to =LOOKUP(1,0/FIND(E2,A$2:A$8), A$2:A$8).

If you don’t understand, you can read the second section of the tutorial "Excel Function Learning: 5 Usage of LOOKUP Function" "Routines for Accurate Search"

Relevant learning recommendations ：excel tutorial

The above is the detailed content of [Organization and Sharing] 4 ways to use VLOOKUP and LOOKUP functions for fuzzy search. For more information, please follow other related articles on the PHP Chinese website!