What type of lookup function are you using VLOOKUP? HLOOKUP? Or...

King of the Lookup function-XLOOKUP function is available now!

With the XLOOKUP function, you can search for a match in a range or array and return the corresponding item through the second range or array.

· The powerful lookup function

Suppose we want to look up Rico's department.

Enter=XLOOKUP, and press the TAB key.

We need to answer three questions for mandatory arguments.

1, what is the keyword for lookup values?Enter Rico followed with a comma.

2, where do we want to search?Select the Name column, and then enter a comma.

3, what to return: the Department column.Press the Enter key. XLOOKUP will update the lookup value if we change the name.

We just find the row of Rico. How can we get her sales in March?

Enter XLOOKUP and enter the lookup value: March.

Enter the lookup array, which is the header row.

Enter the returned array.

Finally, press the Enter key to get the answer.

· Specify the error value

XLOOKUP can return a specified content for a lookup failure.

Take this employee sheet as an example.

Enter a comma after the three required arguments, and specify the contents for the missing value.

Enclose the contents in double-quotes.

Thus, you can return to text values if the lookup value is not found.

· Flexible match modes

XLOOKUP can perform both the exact match and appropriate match. Let's look at four-match codes:

Here's a list of employee performances with scores. Suppose we want to rate them by scores.

The rules on the right side have specified the rating rules.

90-100 is A, 80-89 is B, and so on.

Donny gets a C for ranking because he scores at 75.

Enter XLOOKUP's fifth argument, and we will match the proper grade for all workers.

The value in the Minimum column corresponds to the minimum value of the score range. So we enter -1 as the match mode.

1.Enter =XLOOKUP and press the Tab key.

2. In the first parameter, select cell E4 which contains the score.

3. Select the minimal range H4:H8 as the lookup range in the second argument. Hit F4 to make the range absolute cells.

4. The third parameter will return the grade range J4:J8. Press F4 to make them absolute cells.

5. The fourth parameter is the specified contents for errors. We choose to skip it in this case.

6. The fifth parameter is the match mode. We enter -1 here.

7. p data-imgw=800 data-imgh=450 data-src="//res-academy.cache.wpscdn.com/images/8c5d1ae25248241b21c516f2a0c13c11_0.72_8303.gif" data-test="skks" style="max-width: 100%; width: 720px; max-height: 405px; height: calc(100vw *(450/800)); text-align:center;" >

· Wildcardmatching

Wildcard matching can recognize the symbols * ? and ~   

If we simply enter the first two letters of the department, can wildcards replace the remaining letters and look up the corresponding name?   Let's enter the parameters quickly.

Press the Enter key, and names are matched.  data-imgw=800 data-imgh=450 data-src="//res-academy.cache.wpscdn.com/images/b71ab8412992b2900289fd089f21eef8_0.72_3242.gif" data-test="skks" style="max-width: 100%; width: 720px; max-height: 405px; height: calc(100vw *(450/800)); text-align:center;" >

· The practical search mode

Many novices may mistake the concept of search mode for match mode. XLOOKUp data-imgw=800 data-imgh=450 data-src="//res-academy.cache.wpscdn.com/images/f5176334581d3edd4f65e417425897d4_0.38_6958.png" data-test="skks" style="max-width: 100%; width: 720px; max-height: 405px; height: calc(100vw *(450/800)); text-align:center;" >

Take this employee sheet as an example. Suppose we want to find who checked in on February 4th.

1. Enter =XLOOKUP and press the Tab key.

2. Enter the three necessary arguments.

Clive is returned because he ranks first in this table that enrolled on February 4th.

What if we want to know who the last one that enrolled on the 4th of February is?

Enter three commas in a row, and enter -1 at Search_mode to search from last to first.

This is all about the XLOOKUP function. Did you get it?