How to Use VLOOKUP for fuzzy search in excel?

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.