OmniDocHow to use Excel XLOOKUP function
Explain clearly how to write parameters of XLOOKUP, common usage and the difference between it and VLOOKUP, including searching left, customizing not found prompts and searching from back to front, and giving methods for checking before starting and checking after completion.
Key points of this article
- The basic writing method of XLOOKUP is the three required parameters of search value, search area, and return area, plus optional parameters such as not found prompt.
- Unlike VLOOKUP, XLOOKUP can search to the left, does not require manual column numbers, and formulas are not prone to errors after inserting new columns into the table.
- #N/A is displayed by default when it cannot be found. Filling in the fourth parameter can be changed to a prompt such as "Not Found" to make the table look clearer.
- The number of rows in the search area and the returned area must be consistent, otherwise the formula will report an error, and the reference to the area must be locked before drop-down filling.
Retrieving numbers from another table by job number, name or product number in Excel is something that many people do every week. In the past, most people used VLOOKUP, but it could only search from left to right, and the returned columns had to be counted manually. Once the table was adjusted, errors were prone to occur. XLOOKUP is a lookup function provided in newer versions of Excel that specifically solves these problems.
This article is written for readers who have already used VLOOKUP, or are just starting to learn search functions. After reading it, you can write a complete XLOOKUP formula, know what each parameter means, and handle common cases such as not found, left search, and duplicate values.
How to use XLOOKUP? One sentence conclusion
The basic idea of XLOOKUP is: find a value in one column, and after finding it, return the content in another column in the same row. As long as you write out the search value, search area and return area, the formula will be established; add the remaining parameters as needed.
| your situation | Recommended practices | reason |
|---|---|---|
| Search from the left column and return the content of the right column | Directly use the first three parameters of XLOOKUP | The writing method is intuitive, no series number is needed, and the increase or decrease of columns is not affected. |
| Need to return the left column | Set the return area to the left column | VLOOKUP can't do this, XLOOKUP can |
| To find values that appear repeatedly in multiple rows, look for the last one | The 6th parameter is set to -1 | By default, search from top to bottom will stop at the first item |
| Want to display a prompt instead of an error value when not found | Prompt text for filling in the fourth parameter | The default returns #N/A, which is not intuitive enough. |
| You are using a version of Excel that does not have XLOOKUP | Continue to use VLOOKUP or INDEX plus MATCH combination | Older versions do not support new functions, and formulas may not be opened on other people’s computers. |
Before you start: Confirm these points first
Before taking action, you can avoid a lot of detours by seeing three things clearly.
- Whether the version supports. Enter "=XL" in the cell. If XLOOKUP appears in the drop-down prompt, it means that the current version is available. The old version does not have this function, and the formula will be displayed as an invalid name. Just replace it with VLOOKUP or INDEX plus MATCH.
- Whether the data types of the search value and the search area are consistent. If there are text numbers on one side and numeric values on the other, the search will fail, even if they look the same to the naked eye. This is a common #N/A reason.
- Whether the number of rows in the search area and the return area are the same. The two areas must correspond one-to-one, for example, both from row 2 to row 200. If the number of rows is different, the formula will report an error.
- Does your data have duplicate values. If the same job number appears multiple times, you need to decide whether to take the first or the last one, which will determine how to fill in the sixth parameter.
Steps to use XLOOKUP to find data in Excel
The following example is a scenario: Column A of Table 1 is the job number, Column B is the name, and Column C is the department; Table 2 has the job number, and column D needs to be filled in with the department based on the job number.
- Enter "=XLOOKUP(" in the target cell of Table 2 (such as D2) and start writing the formula.
- The first parameter is the search value. Select the work number cell in Table 2, such as B2. It means "find what you can find".
- The second parameter is the search area, select the job number column in Table 1, for example, A2:A200 in Table 1. It means "in which column to find".
- The third parameter is the return area, select the department column in Table 1, for example, C2:C200 in Table 1. It means "which column to take the content after finding it."
- The fourth parameter is the content displayed when it is not found. Directly enter the prompt text enclosed in quotation marks, such as "Not Found". If you don't write it, #N/A will be displayed when it cannot be found.
- The 5th and 6th parameters are usually omitted. Omitting the fifth parameter means exact matching, which is a common situation; when you need to find duplicate values from back to front, write -1 for the sixth parameter.
- Enter the right bracket and press Enter to confirm the result. Then drag the fill handle downward to copy the formula. Be careful to change the references of the search area and the return area to absolute references with dollar signs, such as $A$2:$A$200 and $C$2:$C$200. Otherwise, the area will move after the drop-down.
- Randomly check a few rows and confirm that the results are consistent with the original table before using them for formal work.
If you need to search to the left, just change the return area to the left column. For example, if you use the name to check the employee number, the formula is written as =XLOOKUP(E2,B2:B200,A2:A200,"No such person"), and the result will return the employee number. This is difficult for VLOOKUP to do directly.
Check each item after completion
Just filling in the formula does not mean that the result is correct. It is recommended to check it item by item.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Whether #N/A or prompt text appears | Check if there are any error values or "not found" in the result column | Confirm that the source table does have the value; check whether there are extra spaces on both sides and whether the text and numerical formats are inconsistent. |
| Is the number of regional rows consistent? | View the starting and ending lines of the search area and return area | Adjust the two areas to the same row range, otherwise the formula will report an error |
| Whether the area moves after the drop-down | After dragging to fill, click on a few cells to see the references. | Add dollar sign to range, lock rows and columns |
| Are the results for duplicate values as expected? | Check the source table to see if the correct one is obtained. | The sixth parameter is set to 1 to get the first one, and set to -1 to get the last one. |
| Whether numbers are saved as text | Whether there is a green triangle in the upper left corner of the cell, or use the ISTEXT function to determine | Unify it into numerical format and then search |
If it is found after checking that there are errors in the data itself, such as duplicate or missing work numbers, you should first correct the source table. The formula itself will not correct the data for you.
📊 Core comparison: The main differences between XLOOKUP and VLOOKUP
Both functions can find and retrieve numbers, but they differ in several key points.
| Comparative item | XLOOKUP | VLOOKUP |
|---|---|---|
| Find directions | You can search left and right | You can only search in the first column of the search area and return to the right |
| Returns how the column is specified | Directly select the return area | Which column of numbers needs to be filled in? It is easy to make mistakes after adding or subtracting columns. |
| What to do when not found | The fourth parameter directly specifies the prompt content | Usually it is necessary to cooperate with functions such as IFERROR |
| Default matching method | Default exact match | The default is approximate matching. For exact matching, you need to write 0 or FALSE, which is easy to miss. |
| Search direction | Can be from front to back or from back to front | Only from top to bottom |
| Version compatibility | Newer version required | Almost all versions are supported |
To put it simply, it is recommended to use XLOOKUP first for new versions of tables; for files that need to be shared by multiple people and whose version is unknown, VLOOKUP or INDEX plus MATCH is more reliable.
❓ Frequently Asked Questions (FAQ)
1. Which one is better, XLOOKUP or VLOOKUP?
Depends on your Excel version and usage scenarios. When the version supports it, XLOOKUP is more trouble-free in terms of left search, column increase and decrease, and not found prompts; when the file needs to be opened in an old version, VLOOKUP has better compatibility.
2. The formula keeps returning #N/A, why?
There are three common reasons: the lookup value does not exist in the source table, the data types on both sides are inconsistent (text and numeric), or there are extra spaces. Check it first with the clipping or search function before deciding whether to correct the data or fill in the 4th parameter.
3. Can XLOOKUP return multiple columns of content at one time?
Can. When returning to a range and selecting multiple columns, the results will automatically expand horizontally to adjacent cells, which is a feature of XLOOKUP. Note that the expanded target area must be left blank, otherwise it will prompt that it cannot be expanded.
4. Can I search according to two conditions at the same time?
Can. A common practice is to add an auxiliary column, connect the two conditions with &, for example =A2&B2, and then use the connected value as the search area and search value. This approach is easy to understand across versions.
📝 Summary
XLOOKUP can complete most searches with three parameters: search value, search area, and return area. The fourth parameter can set the prompt when not found, and the sixth parameter can control which end to start searching from. You can avoid most errors by confirming the version and data format before doing it, and checking the #N/A and area row number after you are done.
