Shulou(Shulou.com)06/01 Report--

This article introduces how to automatically obtain the third parameter of the vlookup function, the content is very detailed, interested friends can refer to, hope to be helpful to you.

For the vlookup function, many people have the idea: why can't the third parameter of the vlookup function be obtained automatically? it's too annoying to need a number. Is there any way to automatically get the third parameter of the vlookup function? Of course, using the match function can achieve the effect of automatically obtaining the third parameter of the vlookup function, and the operation is very simple, so let's learn it together.

1. The function of match function

Match: finds the location of the specified value in the data region

Syntax: = MATCH (lookup value, data region found, matching method)

First parameter: lookup value (lookup_value)

Second parameter: data area found (lookup_array)

The third parameter: search type (match_type). There are three search types in total. A parameter of-1 and 1 represents an approximate match, and a parameter of 0 indicates an exact match. Here, we just need to remember to set the third parameter to 0.

This is the function of this function. Let's take a look at the effect. For example, if we want to find out the position of mathematics in the table header, we just need to set the formula to: = MATCH (J2 ~ A _ 1). Let's briefly introduce the formula to you.

The first parameter: J2, is the location of the cell where the mathematics is located.

The second parameter: A1:H1, the data area of the header in the table

The third parameter: 0, which indicates an exact match

This is the use of the match function. Let's take a look at how it automatically gets the third parameter of the vlookup function.

Second, automatically match the third parameter

As shown in the figure below, if we want to find multi-row and multi-column data, we just need to set the formula to: = VLOOKUP ($K2 match), and then click enter to fill in the formula. The following is a brief introduction to this formula.

Here we nest the matc function into the third parameter of the vlookup function, the formula: = VLOOKUP ($K2).

The first parameter: $K2, look for the value, here we lock the letter label, that is, drag the data position to the right is the same, drag down the data position can change

The second parameter: $A$1:$H$13, the data area to be found. We use an absolute reference to this data area. No matter how much we drag the data, its location will not change.

The third parameter: MATCH. Here we use the match function to find the location of the subjects in the table header to achieve the effect of automatically obtaining the third parameter of vlookup.

Here we lock the numeric label. Dragging to the right will change the position, but dragging down will not change the position. $A$1:$H$1 this is the data area where the header is located. Select absolute reference to prevent its position from changing when dragging the formula.

The fourth parameter: FALSE, which indicates an exact match. You can also replace it with 0.

Using the match function to automatically obtain the third parameter of the vlookup function to quickly find multi-row and multi-column data, in addition to nesting the match function into the third parameter of vlookup, we also need to pay special attention to the way the data is referenced.

On how to automatically get the third parameter of the vlookup function is shared here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.