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

The original title: "more rebellious than Vlookup!" After reading this powerful query function, I knelt down! "

Today we will talk about XLOOKUP, a powerful query function!

Before we talk about the XLOOKUP function, let's take a look at the following case to find the corresponding gender by name.

This problem is all too common! The first thing that comes to mind is to use the VLOOKUP function.

= VLOOKUP (E2Magic A2A2:C7 C7Mag0) finds the value "Wang Wu" of the E2 cell in the first column of the search area A2:C7, and returns the corresponding value "male" in column 2 of the search area A2:C7.

Let's get to know the XLOOKUP function and take a look at the usage of the XLOOKUP function:

= XLOOKUP (value of search, scope of search, range of results)

The formula can be written as follows:

= XLOOKUP (E2Magazine A7MagneB2B2:B7) finds the value "Wang Wu" of the E2 cell in the search range A2:A7, and returns the value "male" corresponding to the search range.

If the name column is not at the front, it seems inappropriate to use the VLOOKUP function. Because the rule of the VLOOKUP function is to look in the first column of the lookup area.

In this case, we usually write the formula with a combination of the INDEX function and the MATCH function:

The MATCH function finds out the line in which the value "Wang Wu" of E2 is in B2:B7, gets the result 3, and then uses the INDEX function to quote the value of line 3 of A2:A7 to get the result "male".

On the other hand, the XLOOKUP function is different. It is not affected by location like VLOOKUP, and it is still used:

= XLOOKUP (E2Magic B2A2:A7 B7MagaA7) finds the value "Wang Wu" of the E2 cell in the search area B2:B7, and returns the value "male" corresponding to the search area.

The fourth parameter of the XLOOKUP function is often asked by a partner, how to make the data that VLOOKUP can't find return to a null value?

As shown in the following figure, if the VLOOKUP function does not find the value "Sun er" of the cell in the first column of the search area A2:B7, it will return the error value # N / A.

= VLOOKUP (D2 ~ A2 ~ B7 ~ 2 ~ 0)

Usually we nest the IFERROR function on the outer layer of the VLOOKUP function, or use the IFNA function for fault tolerance.

= IFNA (VLOOKUP (D2Magazine A2GRO), "")

The XLOOKUP function has its own parameters: its fourth argument is dedicated to fault tolerance.

= XLOOKUP (look up value, search range, return range, [fault tolerance])

This parameter is not required, when you encounter the above problem, it is necessary to ask it out.

The formula can be written as follows:

= XLOOKUP (D2Magazine A7MagneB2Rose B7, "")

Look for the value "Sun er" of the E2 cell in the look-up range A2:A7. If it is found, it returns the corresponding value of B2:B7. If it is not found, it returns the content specified in the fourth parameter.

Of course, the setting of the fourth parameter can not only be a string, a numerical value. You can also nest other formulas to return results.

The fifth argument of the XLOOKUP function follows the rules for evaluation:

Less than 60 points are not qualified.

Greater than or equal to 60 and less than 70 is qualified

Greater than or equal to 70 and less than 80 is good

Greater than or equal to 80 is excellent.

First set the lower limit of the score for each level, as shown in column A below, and then write the formula in cell E2:

= XLOOKUP (D2 for A2 A5 for B2VR, B5 for B5)

The fifth argument of the XLOOKUP function is the matching type.

= XLOOKUP (lookup value, lookup range, return range, [fault tolerance], [matching type])

When the value of the fifth parameter is-1, the next smaller value is returned if the find value is not in look in.

As in the above formula, the value of the D2 cell is 75. If you are not in the search area A2:A7, look for a value less than 75, that is, 70. Then return the grade "good" corresponding to B2:B5.

If you change the lower limit of the score in column A to the upper limit, the formula can be written as follows:

= XLOOKUP (D2 for A2 A5 for B2VR B5 for B5)

When the fifth parameter is 1, the next larger value is returned if the look in value is not in look in.

For example, in the formula, the value of D2 cell is 75. If you are not in the search area A2:A7, look for a value greater than 75, that is, 79. Then return the grade "good" corresponding to B2:B5.

In the end, what are the differences between VLOOKUP, XLOOKUP and LOOKUP? the following Tips is for your reference:

The ❶ VLOOKUP function must look in the first column of the lookup area, and the XLOOKUP function is not restricted by this location

The ❷ VLOOKUP function needs other functions to be nested for fault tolerance, while the XLOOKUP function has its own parameters for fault tolerance, which is more convenient.

❸ LOOKUP function needs ascending sort in multi-valued judgment, while XLOOKUP function does not need to sort.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Zhao Jiaoyang, Editor: Zhu Lan

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.