Hello friends, today we talk about the VLOOKUP function and his brothers.
First of all, let's talk about VLOOKUP, this year, if the office people don't know VLOOKUP, drink yogurt are embarrassed to lick the bottle cap.
The purpose of VLOOKUP is to find the specified content in the leftmost column of the data region and return the contents of the other columns on the right corresponding to it.
A common approach to this function is:
VLOOKUP (who to find, in which area the first column to look up, return the contents of the first column, [matching method])
For example, in the following figure, to query the provincial capital city in the left control table according to the province specified by cell I3, you can use the following formula:
=VLOOKUP(I3,B2:G8,3,0)
The formula indicates that the specified content "Jiangsu" is found in the first column of the B2:G8 area, and the information of the third column of the region is returned, and the fourth parameter uses 0, indicating that an exact match is used.
The fly in the ointment, VLOOKUP function can only query from left to right, if you want to query from top to bottom, you can only ask out VLOOKUP's second brother HLOOKUP.
The syntax of the HLOOKUP function is very similar to that of the VLOOKUP function, and the usage is basically the same.
The difference is that the VLOOKUP function queries in the vertical region, while the HLOOKUP function queries in the horizontal region.
For example, in the following figure, to query the corresponding score in the area of 1 to 3 rows according to the name of cell B5, you can use the following formula to complete:
=HLOOKUP(B5,1:3,3,FALSE)
The formula means that the specified content "Chi Haidong" is found in the first line of the 1:3 area, and the information of the third row of the area is returned, and the fourth parameter uses FALSE, which also means that the exact match is used.
It's not clear what 1:3 means? It is a whole line reference of 1 to 3 rows, and the use of "A:B" to indicate the whole column reference of A to B column is a similar way of writing.
What if you want to query from right to left? Then you have to ask the third brother of VLOOKUP, LOOKUP.
As shown in the following figure, according to the name specified in cell H2, query the name in column C, and return the admission ticket number corresponding to column B:
=LOOKUP(1,0/(C2:C10=H2),B2:B10)
Using the LOOKUP function, you can query data in any direction, and the pattern is written as follows:
=LOOKUP(1,0/(contains a row or column of conditions = specified conditions), row or column to return content)
If you want to query multiple criteria, the pattern is written as:
=LOOKUP(1,0/(Condition range 1=Specify condition 1)/(Condition range 2=Specify condition 2), row or column to return content)
If you're using Office 365, you can also use VLOOKUP's brother XLOOKUP.
This function is written like this:
=XLOOKUP (Lookup Value, Lookup Range, Result Range, [Fault Tolerance Value], [Match Method], [Query Mode])
The first three parameters must be, and the next few parameters can be omitted.
As shown in the following figure, according to the department of G1, query the department in column A and return the name of the person in charge corresponding to column B. The formula is:
=XLOOKUP(G1,A2:A11,B2:B11)
The first parameter is the content of the query, and the second parameter is the area of the query, and the query area only needs to select a column. The third parameter is which column to return, and it is also necessary to select a column.
The meaning of the formula is to find the department specified in cell G1 in the range of cells A2:A11 and return the corresponding name in the range of cells B2:B11.
Since the query area and the return area of the XLOOKUP function are two separate parameters, it is not necessary to consider the direction of the query, not only from left to right, but also from right to left, from bottom to top, from top to bottom and other arbitrary directions of query.
As shown in the following figure, according to the department of G1, query the department in column B, and return the name of the person in charge corresponding to column A. The formula is:
=XLOOKUP(G1,B2:B11,A2:A11)
Using the XLOOKUP function, you can also return the contents of different columns according to the specified query content.
As shown in the following figure, the name, date, and sales amount corresponding to the department in cell G1 are returned respectively. The formula is:
=XLOOKUP(G1,A2:A11,B2:D11)
The third parameter in this formula selects a multi-column range, and since the formula in Office 365 has an overflow function, as long as you enter a formula, you can return multiple information of columns B~D.
Well, that's all we're sharing today, and I wish you all a good day.
Photo and text production: Zhou Qinglin