vlookup function is mainly used to find the corresponding data value of a certain value in the region.
is described with reference to its syntax structure. It means that querys the position of the specified value in the first column of the region and sets the return column of the region to obtain the value of the corresponding position in the reference return column.
Today we will understand the general usage of vlookup function.
So the conventional formula writing method is based on the basic syntax expression of the vlookup function to write formulas.
Its expression is: =vlookup (find value, search area, return column, search type)
It has 4 parameters in total:
The first parameter search value can be text, number or cell reference , etc.;
The second parameter search area is a multi-column area including the query column and the return column, and its query column must be in the first column of the region;
The third parameter returns the column as a number or a function expression can be used to return a numeric value. This parameter is generally called which column it returns . For example, the parameter value 2 means the return column in the second column in the area;
The fourth parameter is the search type. According to different search types, we divide the regular usage of the vlookup function into two parts, namely precise search and approximate search.
Next, we introduce the usage of vlookup function with different search types.
. Accurate search
When the 4th parameter search type of the vlookup function is 0, it means accurate search. Accurate search will query the first parameter search value in the query column and match the corresponding result value in the return column; if the query column does not contain the search value, the formula is calculated incorrectly, and "#NA" is displayed.
Let’s look at the application case below. As shown in the figure below, to query the customer item number of the corresponding product, we can enter the formula as:
=VLOOKUP(P2,E:F,2,0)
Put the formula into the expression, then the P2 cell is the search value, column E:F is the search area, and the result returns to the second column of the area, performing the exact search.
Since it is an accurate search, vlookup directly references the product number corresponding to the product in the search area.
From this case, we can see that the search logic of the vlookup function is very simple. It first finds the location of the search value in the query column, and then finds the corresponding data of the return column at the same horizontal position.
understands this logical rule, which is very helpful for us to extend the application of vlookup function formulas!
2, approximate search
approximate search is usually used in scenarios where the query column does not contain the search value. It has a prerequisite, that is, the query column of the data table is sorted ascending by default. That is to say, to perform approximate search, you need to arrange the data in ascending order first.
For example, in the figure below, you want to query the product "DW220929202", but the product number does not exist in the data table. If you use accurate search, the error value will be returned.
Then we can use approximate lookup and set the 4th parameter of the vlookup function formula to 1.
input formula is:
=VLOOKUP(P2,E:F,2,
)
This formula only changes the fourth parameter search type. 1 represents approximate search, which will query and reference according to the logic that is less than and closest to the search value.
From the above figure, we can see that the smaller and closest value is "DW220929201", so the formula results return the corresponding item number of the product, namely "41407190".
Read more:
Excel table if function is greater than 60 and less than 40, how should it be expressed?
Excel 3 or more if conditions How to set function formulas?
