How to Use VLOOKUP for fuzzy search in excel?

How to Use VLOOKUP for fuzzy search in excel?

Author: iamlaosong

The syntax of this function is as follows:

VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)

Description of input data type

The value, reference, or text string to be searched for by lookup_value.

Table_array region of the region data table to be searched

Col_index_num indicates the number of columns in the region.

Range_lookup: TRUE (or not filled), FALSE

When using this function, the last parameter is generally set to 0 (FALSE), indicating exact matching. If no matching is found, the returned error value # N/A is returned. If 1 is entered, fuzzy match is performed. That is to say, if no exact match value is found, the maximum value smaller than lookup_value is returned.

However, a prerequisite for fuzzy match is that the first column in the search area must be sorted in ascending order; otherwise, the result is unpredictable.

In addition, this search starts from the first character and does not contain any wildcard characters. Therefore, there is no way to match it like "Anhui Post Express logistics company, however, "Anhui Post" is possible.

A function similar to this function is MATCH. The difference is that the former returns the value of the cell, and the latter returns the position of the cell.

This fuzzy query is difficult to use and the return value is not very reliable. Therefore, I think this fuzzy query function is basically useless.

Is there no way? There are still some methods, that is, useThe wildcard + exact match method.

In the above example, "* postal express *" can still be used to match the value of "Anhui postal express logistics company". If it is a cell value, use the connector & connection, for example, in column A of sheet2, fuzzy match the value of cell A1, and retrieve the value of column C after matching:

= VLOOKUP ("*" & A1 & "*", Sheet2! A: C, 3, 0)

Note that the first matching result is returned. The match function has similar functions. For example:

= MATCH ("*" & A1 & "*", Sheet2! A: A, 0)

The returned result is a matching position. Note that the position is the relative position in the region. For example, A10: A100 and A10 are the first position.