how to use xlookup in excel

XLOOKUP is a more powerful and flexible search function in Excel than VLOOKUP. 1. Supports left-facing search, 2. No forced sorting is by default, 3. The formula is more concise, 4. Supports wildcard fuzzy search. Its basic syntax is =XLOOKUP (find value, search array, return array, [content not found], [match method], [search mode]). For example, you can find and return the telephone number of column E based on the name. Usage tips include: 1. Add a prompt not found, such as "not found"; 2. Use "*Technology*" to perform fuzzy search; 3. Use the -1 parameter to find the data closest to and not less than the target value; 4. It can replace the IFERROR VLOOKUP combination to simplify the formula. Mastering these core usages can greatly improve data processing efficiency.

XLOOKUP is a powerful and flexible search function in Excel. Compared with the old versions of VLOOKUP and HLOOKUP, it is more stable, easier to use, and can handle more complex situations. Mastering its basic usage can save you a lot of time.

Basic syntax and parameter description

The basic structure of XLOOKUP is:

 =XLOOKUP (find value, search array, return array, [the content returned when not found], [match method], [search mode])

• Find value : What are you looking for, such as a cell or specific value.
• Find array : In which region is searched, usually a column or row.
• Return array : After finding it, return the data at the corresponding position, which is usually the same as the length of the search array.
• Content not found (optional) : If not found, #N/A will be displayed by default. You can change to "Not Found" or other prompts.
• Matching method (optional) : 0 means exact match, -1 means finding values greater than or equal to, 1 means less than or equal to, this can be adjusted according to requirements.
• Search mode (optional) : The default is 1, which means search from front to back, and 2 means binary search (more efficient), but requires sorting.

For example:

 =XLOOKUP(A2, B2:B10, C2:C10)

It means: find the value of A2 in B2:B10, and then return the corresponding row of data in column C.

Why is it better than VLOOKUP?

Many people are still using VLOOKUP, but XLOOKUP has several obvious advantages:

• Support left search : VLOOKUP can only look right, XLOOKUP can be found wherever you want.
• Default does not force sorting : VLOOKUP If you must sort with approximate matches, XLOOKUP does not need it.
• A more concise formula : Don’t write column numbers like VLOOKUP, it’s not easy to make mistakes.
• Supports wildcard fuzzy search : for example, you can use * or ? to match some content.

For example, you have an employee table that you want to find the phone number by name, name is in column C and phone is in column E:

 =XLOOKUP("Zhang San", C2:C100, E2:E100)

There is no need to count a few more columns, and there is no need to worry that the insertion of the column in the middle affects the result.

FAQs and tips

1. What should I do if I can’t find the content?

The fourth parameter can be added to avoid error prompts:

 =XLOOKUP(A2, B2:B10, C2:C10, "Not Found")

This way, when it cannot be found, it will show "not found", which will look more professional and easier to understand.

2. Support wildcard fuzzy search

If you only know some information, such as the customer name contains "technology", but you are not sure of the full name:

 =XLOOKUP("*Technology*", A2:A100, B2:B100, 0)

Note that you need to add double quotes and enable wildcard matching (the default is).

3. Use the -1 parameter to find the closest match

For example, when you are doing grade query, you want to find students with the closest score but no less than 85 points:

 =XLOOKUP(85, B2:B100, A2:A100, , -1)

Here -1 means finding the smallest of the target value greater than or equal to.

4. Replace the IFERROR VLOOKUP combination with XLOOKUP

We used to use:

 =IFERROR(VLOOKUP(...), "Not Found")

Now just use XLOOKUP, the code is more concise.

Let's summarize

XLOOKUP is indeed a function worth mastering, especially for those who often process table data, which can solve almost most of the problems of finding classes. As long as you remember the basic structure and use it flexibly in combination with actual scenarios, you can greatly improve work efficiency.

Basically, that's all. Although there are many parameters, there are only two or three that are used normally.

The above is the detailed content of how to use xlookup in excel. For more information, please follow other related articles on the PHP Chinese website!