Shulou(Shulou.com)11/24 Report--

The original title: "who says the Vlookup function can only find one column of data?" Match multiple columns, it's fine! "

Hee hee, hello everyone!

I am Yalizi, learn some Excel every day, work efficiency up~up~

A few days ago, Pear encountered such a problem:

When the contract number is known, find its status, estimated delivery time, province and project name from the corresponding large table.

Seeing here, do you want to say just use the VLOOKUP function?

Pear thinks the same way.

After entering the VLOOKUP function, there is no problem filling it down.

But when you fill it to the right, it's all messed up.

It turns out that the VLOOKUP function needs a "helper" here.

It is the MATCH function.

If you want to know how to do it, you can take a look at the tutorial ~ ↓↓↓ first.

1. The MATCH function MATCH function can help us find the position of the target in the row or column.

Its basic syntax is:

= MATCH (search target, search scope, search method)

PS. The third parameter generally enters 0, which indicates precise matching, and 1, which indicates fuzzy matching.

Examples of 👉 operations:

As shown in the figure below, you need to find the location of "gender" in A6:D6.

Enter the formula:

= MATCH (C6Magazine A6 D6j0) Press enter, and the result comes out: 3.

The parameter meaning of the MATCH function is:

The first parameter: C6, that is, finding the target

The second parameter: A6:D6, which is the search scope

The third parameter: 0, the way to find it.

The MATCH function is done, but how does it work with the VLOOKUP function?

Next, let's take a look at 👇.

2. VLOOKUP multiple matching will nest MATCH in the VLOOKUP function, and it will become the third parameter.

Formula:

= VLOOKUP ($A15 recordings, Atrop2purses, Agg10Magi match), 0)

Meaning of the formula:

The first parameter: $A15, which is the search target.

The second parameter: $A$2:$A$10, which is the search scope

The third parameter: MATCH, which is the search location (the number of columns in which the data is located).

The fourth parameter: 0, which indicates how to find it.

Expand:

It is also mentioned in the formula that you need to add a relative reference to the lookup target of the two functions; add an absolute reference to the lookup scope.

The aim is to fix the ranks of the search range so that the formula does not produce errors.

3. Summarize the combination of VLOOKUP+MATCH functions, which can solve the problem of one-to-many search.

However, it is important to pay attention to the Match function, which can match the data in the case of different order of the headers of the data source, but the contents of the headers should be consistent and cannot be separated by a word.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Yalizi

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.