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

The original title: "10 times easier to use than the Vlookup function!" Xlookup is the NO.1 of the query function. "

Hello, everyone, Hi~. I am Tian Tian who has come to share the practical knowledge of Excel with you again.

As we all know: learn Excel skills to improve the efficiency of 80%, make good use of the Excel function one button to get off work early!

Recently, many students have left us a message backstage: "I am a beginner, can you tell me something about the Excel function?" "

Of course. ~ Ding Dong! Akiba favourite fan new column [0 basic Excel function] is coming!

We will share with you the skills of using a function in each issue, and teach you Excel hand in hand!

All right, let's get to the point. Today, let's learn about a very useful query function-- the XLOOKUP function.

1. The main function of knowing xlookup function is to help you find data. To make good use of it, you only need to remember three tips:

According to who?

Where can I find it?

Where is the object you are looking for?

Let's take a look at the basic usage of the XLOOKUP function:

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

2. Let's take a look at case 1 for regular search. Now we have to find their corresponding amount from the contract list according to these numbers to be checked. What should we do?

❶ input = XLOOKUP. After the target function appears, press TAB to insert it quickly.

❷ recalls 3 sentences of formula and completes the corresponding operation according to the formula.

According to who? Find [contract amount] according to [contract number]. Therefore, directly select the D4 cell, and then enter the English comma "," separated from the following parameters.

Where can I find it? Look for it in [contract number]. Select the A4:A11 cell range and type a comma.

Where is the person you are looking for? Change [contract amount]. Select the data in the contract amount column (B4:B11), put in anti-parentheses, press enter, and the result will come out.

The complete formula is as follows:

Notice that the range of cells referenced in the function formula has the property of "relative reference". = XLOOKUP (D4Magazine A11MagneB4 B11) note that the range of cells referenced in the function formula has the property of "relative reference".

The three regions we have just selected will shift relative to the location of our formula, which will cause the query area and result area to deviate from the location of the data source, resulting in an error in the result.

Therefore, before filling down, you need to lock the [second area] and [third area] so that they do not move.

❸ double-click the cell to enter formula editing mode, select [second parameter] and [third parameter] respectively, and press [F4] to lock it.

The complete formula is as follows:

= XLOOKUP (D4grammage of formula 4) so that when we fill the formula down again, only the queried object will move along with the formula cell, and the result will be displayed correctly.

3, reverse search case 2, the contract number ran to the left side of the contract amount, what should be done? Don't panic, we respond to changes with the same attitude.

❶ input = Xlookup function, put in parentheses, according to who to find, select the [contract number] to be checked. Where to find it, select the data in the contract number column.

Where is the person ❷ is looking for? It's in the contract amount, of course, so select the data in the contract amount column. Don't forget to lock the second and third parameters, press enter, then fill it down and get it done.

The complete formula is as follows:

4. Vertical search, let's take a look at the third case. The contract number in this data source has gone upstairs. Does it matter? In fact, there is no impact! Let's go straight to functional magic.

❶ input = XLOOKUP function, enclose parentheses, and select the data in the [contract number] and [contract number] lines to be checked according to who is looking for it and where to find it.

Where is the person ❷ is looking for? It is obviously in the contract amount line, so select the data in the contract amount line. Lock the parameters, press enter, and then [fill down], and the results are displayed.

The complete formula is as follows:

= XLOOKUP (A31thecontrolling B27VERVOLIZING 27ZHANG 28VOB28) OK, have you learned all the ways to use the XLOOKUP function? Is it super simple? let's go and try it.

Unfortunately, the XLOOKUP function can only be used in Office 365 and the latest version of WPS. If you are using another version, you can try INDEX+MATCH instead and click on the text link below to jump to the tutorial: more powerful than Vlookup! This function combination, people who have used it all say incense, miss regret 100 million!

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

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.