by The tutorial introduces XLOOKUP - the new function for vertical and horizontal lookup in Excel. Left lookup, last match, Vlookup with multiple criteria and a lot more things that used to require a rocket science degree to accomplish have now become as easy as ABC.
Whenever you need to look up in Excel, which function would you use? Is it a cornerstone VLOOKUP or its horizontal sibling HLOOKUP? In a more complex case, will you rely on the canonical INDEX MATCH combination or commit the job to Power Query? The good news is that you do not have choose anymore - all these methods are making way for a more powerful and versatile successor, the XLOOKUP function.
How is XLOOKUP better? In many ways! It can look vertically and horizontally, to the left and above, search with multiple criteria, and even return a whole column or row of data, not just one value. It has taken Microsoft over 3 decades, but finally they've managed to design a robust function that overcomes many frustrating errors and weaknesses of VLOOKUP.
What's the catch? Alas, there is one. The XLOOKUP function is only available in Excel for Microsoft 365, Excel 2021, and Excel for the web.
Excel XLOOKUP function - syntax and uses
The XLOOKUP function in Excel searches a range or an array for a specified value and returns the related value from another column. It can look up both vertically and horizontally and perform an exact match (default), approximate (closest) match, or wildcard (partial) match.
The syntax of the XLOOKUP function is as follows:
The first 3 arguments are required and the last three are optional.
- Lookup_value - the value to search for.
- Lookup_array - the range or array where to search.
- Return_array - the range or array from which to return values.
- If_not_found [optional] - the value to return if no match is found. If omitted, an #N/A error is returned.
- Match_mode [optional] - the match type to perform:
- 0 or omitted (default) - exact match. If not found, an #N/A error is returned.
- -1 - exact match or next smaller. If an exact match is not found, the next smaller value is returned.
- 1 - exact match or next larger. If an exact match is not found, the next larger value is returned.
- 2 - wildcard character match.
- Search_mode [optional] - the direction of search:
- 1 or omitted (default) - to search from first to last.
- -1 - to search in reverse order, from last to first.
- 2 - binary search on data sorted ascending.
- -2 - binary search on data sorted descending.
According to Microsoft, binary search is included for advanced users. It is a special algorithm that finds the position of a lookup value within a sorted array by comparing it to the middle element of the array. A binary search is much faster than a regular search but works correctly only on sorted data.
Basic XLOOKUP formula
To gain more understanding, let's build an XLOOKUP formula in its simplest form to perform an exact lookup. For this, we will only need the first 3 arguments.
Supposing, you have a summary table with information about the five oceans on the Earth. You want to get the area of a specific ocean input in G4 (lookup_value). With the ocean names in B4:B8 (lookup_array) and areas in D4:D8 (return_array), the formula goes as follows:
=XLOOKUP(G4, B4:B8, D4:D8)
Translated into plain English, it says: search for the G4 value in B4:B8 and return a value from D4:D8 in the same row. No column index numbers, no sorting, no other ridiculous quirks of Vlookup! It just works :)
If needed, you can "hardcode" the lookup value directly in a formula like this:
=XLOOKUP("Indian", B4:B8, D4:D8)
XLOOKUP availability
As of now, the XLOOKUP function is available in Excel 365, Excel 2021, and Excel for the web. It's important to note that XLOOKUP is not backward compatible, meaning it won't function in earlier versions.
How is XLOOKUP better than VLOOKUP?
XLOOKUP is a powerful function that surpasses VLOOKUP in many aspects. It offers more capabilities, versatility and efficiency. Here are the top 10 features that make it the ultimate lookup function in Excel:
- Vertical and horizontal lookup. The XLOOKUP function got its name due to its ability to look up both vertically and horizontally.
- Look in any direction: right, left, bottom or up. While VLOOKUP can only search in the leftmost column and HLOOKUP in the topmost row, XLOOKUP has no such limitations. The notorious left lookup in Excel is not a pain anymore!
- Exact match by default. In most situations, you will be looking for an exact match, and XLOOKUP returns it by default (unlike the VLOOKUP function that defaults to approximate match). Of course, you can get XLOOKUP to perform an approximate match too if needed.
- Partial match with wildcards. When you know only some part of the lookup value, not all of it, a wildcard match comes in handy.
- Search in reverse order. Earlier, to get the last occurrence, you had to reverse the order of your source data. Now, you simply set the search_mode argument to -1 to force your Xlookup formula to search from the back and return the last match.
- Return multiple values. By manipulating with the return_array argument, you can pull an entire row or column of data related to your lookup value.
- Search with multiple criteria. Excel XLOOKUP handles arrays natively, which makes it possible to perform lookup with multiple criteria.
- If error functionality. Traditionally, we use the IFNA function to trap #N/A errors. XLOOKUP incorporates this functionality in the if_not_found argument allowing to output your own text if no valid match is found.
- Column insertions/deletions. One of the most irritating issues with VLOOKUP is that adding or removing columns breaks a formula because the return column is identified by its index number. With XLOOKUP, you supply the return range, not number, meaning you can insert and remove as many columns as you need without breaking anything.
- Better performance. VLOOKUP could slow down your worksheets because it includes the entire table in calculations, which results in processing far more cells than actually needed. XLOOKUP handles only the lookup and return arrays that it truly depends on.
For more information, please see Difference between VLOOKUP and XLOOKUP.
How to use XLOOKUP in Excel - formula examples
The following examples demonstrate the most useful XLOOKUP features in action. Additionally, you will discover a couple non-trivial uses that will take your Excel lookup skills to a new level.
Look up vertically and horizontally
Microsoft Excel used to have two functions for different lookup types, each having its own syntax and usage rules: VLOOKUP to look vertically in a column and HLOOKUP to look horizontally in a row.
The XLOOKUP function can do both with the same syntax. The difference is in what you provide for the lookup and return arrays.
For v-lookup, supply columns:
=XLOOKUP(E2, A3:A7, B3:B7)
For h-lookup, enter rows instead of columns:
=XLOOKUP(I1, B1:F1, B2:F2)
Left lookup performed natively
In earlier versions of Excel, INDEX MATCH formula was the only reliable way to look to the left or above. Now, you no longer need to combine two functions where one would suffice. Just specify the target lookup array, and XLOOKUP will handle it without a problem regardless of its location.
As an example, let's add the Rank column to the left of our sample table. The goal is to get the rank of the ocean input in F1. VLOOKUP would stumble here because it can only return a value from a column to the right of the lookup column. An Xlookup formula copes with ease:
=XLOOKUP(F1, B2:B6, A2:A6)
In a similar manner, you can look above when searching horizontally in rows.
XLOOKUP with exact and approximate match
The match behavior is controlled by the 5th argument called match_mode. By default, an exact match is performed.
Please pay attention that even when you choose an approximate match (match_mode set to 1 or -1), the function will still search for an exact match first. The difference is in what it returns if an exact lookup value is not found.
Match_mode argument:
- 0 or omitted - exact match; if not found - #N/A error.
- -1 - exact match; if not found - next smaller item.
- 1 - exact match; if not found - next larger item.
Exact match XLOOKUP
This is the option you probably use 99% of the time you do lookup in Excel. Since an exact match is XLOOKUP's default behavior, you can omit match_mode and supply only the first 3 required arguments.
In some situations, however, an exact match won't work. A typical scenario is when your lookup table does not contain all the values, but rather "milestones" or "bounds" like quantity-based discounts, sales-based commissions, etc.
Our sample lookup table shows the correlation between exam scores and grades. As you can see in the screenshot below, an exact match works only when the score of a particular student matches the value in the lookup table exactly (like Christian in row 3). In all other cases, an #N/A error is returned.
=XLOOKUP(F2, $B$2:$B$6, $C$2:$C$6)
To get the grades instead of #N/A errors, we need to look for an approximate match as shown in the next example.
Approximate match XLOOKUP
To perform an approximate lookup, set the match_mode argument to either -1 or 1, depending on how your data is organized.
In our case, the lookup table lists the lower bounds of the grades. So, we set match_mode to -1 to search for the next smaller value when an exact match is not found:
=XLOOKUP(F11, $B$11:$B$15, $C$11:$C$15, ,-1)
For example, Brian has the score of 98 (F2). The formula searches for this lookup value in B2:B6 but cannot find it. Then, it searches for the next smaller item and finds 90, which corresponds to grade A:
If our lookup table contained the upper bounds of the grades, we would set match_mode to 1 to search for the next larger item if an exact match fails:
=XLOOKUP(F2, $B$2:$B$6, $C$2:$C$6, ,1)
The formula searches for 98 and again cannot find it. This time, it tries to find the next larger value and gets 100, corresponding to grade A:
Tip. When copying an Xlookup formula to multiple cells, lock the lookup or return ranges with absolute cell references (like $B$2:$B$6) to prevent them from changing.
XLOOKUP with partial match (wildcards)
To perform a partial match lookup, set the match_mode argument to 2, which instructs the XLOOKUP function to process the wildcard characters:
- An asterisk (*) - represents any sequence of characters.
- A question mark (?) - represents any single character.
To see how it works, please consider the following example. In column A, you have a few smartphone models and, in column B, their battery capacity. You are curious about the battery of a certain smartphone. The problem is that you are not sure you can type the model name exactly as it appears in column A. To overcome this, enter the part that is definitely there and replace the remaining characters with wildcards.
For example, to get information about the battery of iPhone X, use this formula:
=XLOOKUP("*iphone X*", A2:A8, B2:B8, ,2)
Or, input the known part of the lookup value in some cell and concatenate the cell reference with the wildcard characters:
=XLOOKUP("*"&E1&"*", A2:A8, B2:B8, ,2)
XLOOKUP in reverse order to get last occurrence
In case your table contains several occurrences of the lookup value, you may sometimes need to return the last match. To have it done, configure your Xlookup formula to search in reverse order.
The direction of search is controlled be the 6th argument named search_mode:
- 1 or omitted (default) - searches from first to last value, i.e. top-to-bottom with vertical lookup or left-to-right with horizontal lookup.
- -1 - searches in reverse order from last to first value.
As an example, let's return the last sale made by a specific salesperson. For this, we put together the first three required arguments (G1 for lookup_value, B2:B9 for lookup_array, and D2:D9 for return_array) and put -1 in the 5th argument:
=XLOOKUP(G1, B2:B9, D2:D9, , ,-1)
Straightforward and easy, isn't it?
XLOOKUP to return multiple columns or rows
One more amazing feature of XLOOKUP is its ability to return more than one value relating to the same match. All is done with the standard syntax and without any extra manipulations!
From the below table, supposing you want to retrieve all the details pertaining to the salesperson in F2. What you need to do is supply a range containing several columns (B2:D7) for the return_array argument:
=XLOOKUP(F2, A2:A7, B2:D7)
You enter the formula in the top-left cell of the results range, and Excel automatically spills the results into adjacent blank cells. In our case, the returned array (G2:I2) consists of 1 row and 3 columns (Date, Item and Amount). Looking from another angle, we can say that XLOOKUP returns an entire row of data relating to the lookup value.
If you prefer to arrange the results vertically in a column, nest XLOOKUP into the TRANSPOSE function to flip the returned array:
=TRANSPOSE(XLOOKUP(G1, A2:A7, B2:D7))
To return values from non-adjacent columns, nest the FILTER function in the return_array of XLOOKUP. A horizontal array {1,0,1} served to the include argument of FILTER determines which columns to filter out (0s) and which to return (1s).
=XLOOKUP(F2, A2:A7, FILTER(B2:D7, {1,0,1}))
It should be noted, however, that XLOOKUP cannot return multiple columns when the lookup_value argument is a range. In this case, you can use the combination of the CHOOSEROWS and XMATCH functions:
=CHOOSEROWS(B2:C10, XMATCH(E2:E4, A2:A10))
Similarly, you can return values from multiple rows when performing a horizontal lookup. To put it differently, you can return an entire column of data relating to your lookup value.
For example, to return all the values from the Amount column, use "Amount" (F1) as lookup_value, the range A1:D1 containing the column headers as lookup_array, and the range A2:D7 as return_array.
=XLOOKUP(F1, A1:D1, A2:D7)
It’s important to ensure that you have enough blank cells to the right or down when using XLOOKUP to return multiple values, as a #SPILL! error will occur if there are not enough empty cells.
Note. If your lookup array contains multiple occurrences of the lookup value and you aim to return all matches, XLOOKUP cannot do that. In this case, you can use the FILTER function or the combination of INDEX, SMALL and ROW functions as demonstrated in this tutorial: How to Vlookup multiple matches in Excel.
Tip. XLOOKUP can also be used to search for and replace values with other values that you specify. A example of such bulk replace can be found here: How to search and replace multiple values with XLOOKUP.
XLOOKUP with multiple criteria
Another big advantage of XLOOKUP is that it handles arrays natively. Due to this ability, you can evaluate multiple criteria directly in the lookup_array argument:
How this formula works: The result of each criteria test is an array of TRUE and FALSE values. The multiplication of the arrays converts TRUE and FALSE into 1 and 0, respectively, and produces the final lookup array. As you know, multiplying by 0 always gives zero, so in the lookup array, only the items that meet all the criteria are represented by 1. And because our lookup value is "1", Excel takes the first "1" in lookup_array (first match) and returns the value from return_array in the same position.
To see the formula in action, let's pull an amount from D2:D10 (return_array) with the following conditions:
- Criteria1 (date) = G1
- Criteria2 (salesperson) = G2
- Criteria3 (item) = G3
With dates in A2:A10 (criteria_range1), salesperson names in B2:B10 (criteria_range2) and items in C2:C10 (criteria_range3), the formula takes this shape:
=XLOOKUP(1, (B2:B10=G1) * (A2:A10=G2) * (C2:C10=G3), D2:D10)
Though the Excel XLOOKUP function processes arrays, it works as a regular formula and is completed with a usual Enter keystroke.
The XLOOKUP formula with multiple criteria is not limited to "equal to" conditions. You are free to use other logical operators as well. For example, to filter orders made on the date in G1 or earlier, put "<=G1" in the first criterion:
=XLOOKUP(1, (A2:A10<=G1) * (B2:B10=G2) * (C2:C10=G3), D2:D10)
For more examples, please see How to use XLOOKUP multiple criteria.
Double / nested XLOOKUP
To find a value at the intersection of a certain row and column, perform the so-called double lookup or matrix lookup. Yep, Excel XLOOKUP can do that too! You simply nest one function inside another:
How this formula works: The formula is based on XLOOKUP's ability to return an entire row or column. The inner function searches for its lookup value and returns a column or row of related data. That array goes to the outer function as the return_array.
For this example, we are going to find the sales made by a particular salesperson within a certain quarter. For this, we enter the lookup values in H1 (salesperson name) and H2 (quarter), and do a two-way Xlookup with the following formula:
=XLOOKUP(H1, A2:A6, XLOOKUP(H2, B1:E1, B2:E6))
Or the other way round:
=XLOOKUP(H2, B1:E1, XLOOKUP(H1, A2:A6, B2:E6))
Where A2:A6 are the salesperson names, B1:E1 are quarters (column headers), and B2:E6 are data values.
A two-way lookup can also be performed with an INDEX Match formula and in a few other ways. For more information, please see Two-way lookup in Excel.
If Error XLOOKUP
When the lookup value is not found, Excel XLOOKUP returns an #N/A error. Quite familiar and understandable to expert users, it might be rather confusing for novices. To replace the standard error notation with a user-friendly message, type your own text into the 4th argument named if_not_found.
Back to the very first example discussed in this tutorial. If someone inputs an invalid ocean name in E1, the following formula will explicitly tell them that "No match is found":
=XLOOKUP(E1, A2:A6, B2:B6, "No match is found")
Notes:
- The if_not_found argument traps only #N/A errors, not all errors.
- #N/A errors can also be handled with IFNA and VLOOKUP, but the syntax is a bit more complex and a formula is lengthier.
Case-sensitive XLOOKUP
By default, the XLOOKUP function treats lowercase and uppercase letters as the same characters. To make it case-sensitive, use the EXACT function for the lookup_array argument:
How this formula works: The EXACT function compares the lookup value against each value in lookup array and returns TRUE if they are exactly the same including the letter case, FALSE otherwise. This array of logical values goes to the lookup_array argument of XLOOKUP. As the result, XLOOKUP searches for the TRUE value in the above array and returns a match from the return array.
For example, to get the price from B2:B7 (return_array) for the item in E1 (lookup_value), the formula in E2 is:
=XLOOKUP(TRUE, EXACT(E1, A2:A7), B2:B7, "Not found")
Note. If there are two or more exactly the same values in the lookup array (including the letter case), the first found match is returned.
Excel XLOOKUP not working
If your formula does not work right or results in error, most likely it's because of the following reasons:
XLOOKUP is not available in my Excel
The XLOOKUP function is not backward compatible. It's only available in Excel for Microsoft 365 and Excel 2021, and won't appear in earlier versions.
XLOOKUP returns wrong result
If your obviously correct Xlookup formula returns a wrong value, chances are that the lookup or return range "shifted" when the formula was copied down or across. To prevent this from happening, be sure to always lock both ranges with absolute cell references (like $A$2:$A$10).
XLOOKUP returns #N/A error
An #N/A error just means the lookup value is not found. To fix this, try searching for approximate match or inform your users that no match is found.
XLOOKUP returns #VALUE error
A #VALUE! error occurs if the lookup and return arrays have incompatible dimensions. For instance, it is not possible to search in a horizontal array and return values from a vertical array.
XLOOKUP returns #REF error
A #REF! error is thrown when looking up between two different workbooks, one of which is closed. To fix the error, simply open both files.
As you have just seen, XLOOKUP has many awesome features that make it THE function for almost any lookup in Excel. I thank you for reading and hope to see you on our blog next week!
Practice workbook for download
Excel XLOOKUP formula examples (.xlsx file)
160 comments
Hello, how can I use xlookup if I wanted to return the value of a text that belongs to a cell with multiple other text:
How do I complete this:
Invoice Number With Waiver? Waiver Type Waiver Status
1234
3456
5678
7890
Using below data:
Waiver Type Status Invoice Numbers
Commercial Denied 5678
Technical Approved 7890,3456,1234
Hi! If I understand your task correctly, carefully read the paragraph in the article above: XLOOKUP to the left. It covers your case completely. For example:
=XLOOKUP(F1, C2:C6, A2:A6)
F1 = Invoice Number.
Can I use XLOOKUP to interpolate values between identified table column entries? For instance, I have a big data table where a small part of it looks something like the following:
Column A Column B Column C
10 100 1
10 300 2
20 200 4
20 400 5
I want to lookup what an interpolated Column C value would be given a lookup value using column A = 15 and Column B = 220
Hi! Your task is not completely clear to me. What result do you want to get? Give an example of the expected result.
Is there any way you can lookup a value, that you not know the name of yet?
I have a data sheet that looks a bit like this:
Level 1 | Level 2 | Level 3
--------------------------------------------
A | B | A
A | A | A
0 | A | 0
A | C | A
The return column looks a bit like this:
Computer 1 | Computer 2
A | B
A | -
A | -
A | C
The problem is that I know the value (text string) of A, but B and C can be any out of a list of 15, there might be even a D and an E.
For the first return column I have no issues, but I'm not sure how to handle the second, since I don't know which value B or C represent (at least not in a formula)
Hi! Please clarify your specific problem or provide additional information to understand what you need. I can't understand how the source data and the desired result are related.
So I get a matrix (10 Colums x 18 Rows) of levels, which are named, A through O;
But the matrix isn't always completely filled, there might be empty spots;
I have to assign those levels to a computer, 1 through 4, on 18 corresponding rows; (so basically a 4x18 matrix)
Duplicates is not a problem (so if one row has A twice, that's not a big deal);
Computer 1 and 2 handle 'level A' and 'level B' by default, respectivly;
So I just check each row, if they contain 'level A', then it's assigned to computer 1 , same for 'level B';
(done through X.LOOKUP(A;row 1; row 1; "")
Now for the problem;
Any of these rows might contain level C through O, but it's not a certainty;
What has to happen is, IF a row contains level C (for example), it has to be filled into that row, on Computer 3.
My current solution is:
IF.ERROR(FILTER(X.LOOKUP('list of levels array';'row in which to look;"error message");X.LOOKUP('list of levels array';'row in which to look"");"")
For now this seems to work, but it's going to give overflow errors when there's more than one unique level. (the list of levels array doesn't contain A or B).
I hope this clarifies the problem enough, I hope you can think of a better solution.
Thanks in advance!
Hi! I’m not sure I got you right, since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:
=TRANSPOSE(UNIQUE(TRANSPOSE(FILTER(A1:D1,A1:D1<>""))))
Copy it down along the column.
The following tutorial should help: Get unique values in Excel ignoring blanks.
Thanks! It seems to do the trick, just need to sort them out in orde, but that shouldn't be to hard.
Hello,
Excellent information provided, however I can't seem to build the formula to work for what I need.
I have a table with three different headers determining information, laid out as below:
| SUPPORT | SUPPORT | SECURITY | SECURITY
-------------------------------------------------------------------
| Full | Co-op | Full | Co-op
-------------------------------------------------------------------
JAN | 10 | 20 | 30 | 40
FEB | 15 | 45 | 50 | 45
I am trying to use XLOOKUP to pull the numbers by matching the data in the first Column, and the first two Rows, i.e: I want to be able to pull the number for 'Full' 'SUPPORT' 'JAN', and pull a different number for 'Co-op' 'SUPPORT' 'JAN'
I don't think this is actually possible the way I'm trying at least, but would appreciate anyone's advice on how to make it work.
Hello! The XLOOKUP function cannot help you in your task. It cannot select several columns by the first condition and then select among them the required column by the second condition. Use FILTER function to select a column in the table that matches both conditions. Then use INDEX MATCH to find the third condition in column A and extract the corresponding value.
=INDEX(FILTER(B1:E4,(B2:E2=H2)*(B1:E1=H1)),MATCH(H3,A1:A4,0),1)
H1= SUPPORT
H2= Co-op
H3= FEB
Hope this is what you need.
Hi,
I really appreciate this amazing tutorial, so thank you very much.
I have written an Xlookup with multiple criteria and it’s going well - only thing is that in the return array I want the largest value to be found (not the first value). Is there a way I can go about doing this without the changes being too major?
Thanks again,
Hi! I don't know what formula you are using. If the XLOOKUP formula returns an array of numbers, you can find the maximum value using the MAX function.
MAX(XLOOKUP(1, (criteria_range1=criteria1) * (criteria_range2=criteria2) * (…), return_array))
Hi, thank you so much for responding.
Just before I wrote this reply I tried what you suggested and it returned the first value rather than the largest value - it’s nice to know I was on a similar wavelength so must be close!
With my formula, I need to return the most recent date in another grid that fulfils 2 criteria (that are in other columns of the same row of that grid). The dates column on that grid is not in descending order and what the formula above returned was the first date in the grid whereby the criteria in the other 2 columns were met (it returned the date that had been inputted on the lowest row number where the conditions were met, rather than returning the most recent date where the conditions are met, and the most recent date should be the largest number in that column where the conditions are met).
I really appreciate your response above - it’s very kind of you to be responding to others who are teaching themselves excel. Is there a way you think that I can adapt the formula above so that it can fulfil its requirements of returning the largest (most recent) date rather than the first date whereby the conditions are met?
Hope you had a great festive period and new year.
Many thanks
Josh
Hi! It would be good if you described the problem in detail in your first question. If I understand correctly, try a formula like this:
=MAX(IFERROR(INDEX($A$2:$A$11,SMALL(IF(1=((--($D$1=$B$2:$B$11))*(--($E$1=$C$2:$C$11))),ROW($A$2:$A$11)-1,""),ROW(A1:A10))),""))
The criteria are written in D1 and E1. The dates are written in column A. You can find the examples and detailed instructions here: Vlookup multiple matches based on multiple conditions. The formula returns all dates that match the conditions, and the MAX function shows the greatest date.
To get an array of dates that match the criteria, you can use the FILTER function.
=MAX(FILTER(A2:A11,($D$1=$B$2:$B$11)*($E$1=$C$2:$C$11),""))
Read more: Excel FILTER function - dynamic filtering with formulas.
Thank you so much for your replies, I’ve entered a rabbit hole where I’m learning loads of new formulas and I love it! Thank you so much - I really appreciate the time you’ve taken to help.
Hello! I am trying to match the first several digits in a 16 digit number to specific words (I have a mapping tab where the numbers coincide to specific words). The problem is sometimes its the first 4 digits, and other times its the first 6 digits. I can't do an xlookup for the first 6 digits because the in the case of the 4 digit identifiers, the next digits aren't always the same. Also, I don't think I can use approximate match because I that only uses smaller or larger matches.
Hi! To ensure clear understanding of your task, can you provide an example of the source data and the desired result you are aiming for?
Hello, really like your article.
As a newbie to the XLOOKUP function I understand the basics, where I am struggling is when I have a set of two criteria (Data & Employee number) that need to search against two different spreadsheet tabs. One tab contains a list of company employees and the other tab a list of contractors. I would like the formula to basically look at the two criteria and search the company employees tab, and if not found search the contractor tab.....how might that formula look?
Any help would be greatly appreciated!
Thanks
Realized I did not explain what I am looking for....I am trying to identify the name of the person who matches the two criteria (Date & Employee number). Each tab (company employees and contractors) has a date and number applied to there names. I am trying to pull that information, on another tab, into a single list of names containing both company employees and contractors using the XLOOKUP function.
If you want to extract multiple values from each sheet based on two criteria, I suggest using these guidelines: How to Vlookup multiple values in Excel with criteria. To get a list of names, we can also use the FILTER function as described in this article: Excel FILTER function - dynamic filtering with formulas.
Hi! I recommend reading this guide: VLOOKUP with IF statement in Excel and VLOOKUP across multiple sheets in Excel with examples. You can do the same with the XLOOKUP function.
Could you please help with these questions? 1. How not to lose value that is already in a field when xlookup performed between two workbooks?
2. Where is 0 or 1/10/1900 is coming from after xlookup, if the field was blank?
3. Why can’t I copy and paste to get rid of the formula and just save values on the filtered sheet. I have more than 52000 rows, but I need to copy paste only on 150 of the them, that are filtered.
Any help would be greatly appreciated!
Thank you!
Hi! Please take a look at this article: How to copy filtered data in Excel. The rest of your questions, unfortunately, are not very clear. Also, I can't see your data.
Hi Alexander,
Thank you for a quick response! I tried the advice from the article to (Go to Special > Visible cells only), but I still receive the same error message, after I do ctrl+v “The action won’t work on multiple selections”.
As for the rest of my questions: I performed xlookup from one workbook to another, and came across to these issues: Some of the blank cells became 0 some 1/0/1900, I am not sure why this is happening.
Thanks a lot for your help!
Hi! When performing actions on empty cells, Excel often converts them to 0. A value of 1/0/1900 is a 0 in date format.
Hi Team, your Xlookup session is valuable,
I have a query, hoping to have solution
In Xlookup, how about if the lookup_value is formula.
Scenario - i have simple table for time to be reported which is 4X3 grid with a header "Now()", I try to get result based on header but i see error.
Any solutions please !!
Keerthy
Hi! The lookup_value can be a formula. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used, and what error occurred. Give an example of the source data and the expected result.
A very good tutorial. A useful addition to your article (which I was looking for, but since found a solution using SEQUENCE) would be returning a 2-D array.
Perfect Thanks
Hi
how to update xlookup formula in office 2019
Hello,
I am asking XLOOKUP to do a lookup only I get the #NAME?. I honestly can't see what is wrong with my formula.
=XLOOKUP(K:K,'GPS-Data'!A:A,'GPS-Data'!B:B)
Hi! This #NAME? error means that there is no such function in your Excel. See the function description at the beginning of this article.
Hello. This is so useful, thank you.
Is there a way of getting the XLOOKUP to look at and return a value from a different range if the range it is looking at is empty? For example, I am asking it to return a value from B4:T4, by looking up K61 in B12:T12. If B12:T12 is empty, i'd like it to look and to return a value from B9:T9 instead. Is this possible?
My main formula is =XLOOKUP(K61,B12:T12,B4:T4,"") and to solve my problem I have tried =IF(XLOOKUP(K61,B12:T12,B4:T4)="",XLOOKUP(K61,:B9:T9,B4:T4)) but it keeps returning NA when there is definitely a value in B9:T9. Any help greatly appreciated. Thanks
Hello! The following tutorial should help: IF VLOOKUP in Excel: Vlookup formula with If condition. You can do the same with the XLOOKUP function. For example,
=IF(ISNA(XLOOKUP(K61,B12:T12,B4:T4)), XLOOKUP(K61,B9:T9,B4:T4), XLOOKUP(K61,B12:T12,B4:T4))
I hope it’ll be helpful.
Hi,
=XLOOKUP("*B1*",Ordering!L:L,Ordering!A:A, "", 2)
How would I adjust that formula so that the search key(from a drop down menu in B1) returns partial matches eg. JER / TK. from the search range?
Hi! To find a partial match in text values, use these guidelines: How to find substring in Excel. For example:
=XLOOKUP(TRUE,ISNUMBER(SEARCH("B1",Ordering!L:L,Ordering!A:A)), "", 2)
Thank you very much Svetlana for posting the example workbook for practice,
I have been greatly assisted to easily extract interval data from a huge dataset after following your tutorial.
Great thanks indeed🍕👌
Hi! I am trying to create a formula using XLOOKUP that can generate a column average and also work with different quantities of columns (this formula will address survey responses, so, one worksheet may have 10 columns that need to be "looked up", and one may have three). Is there a formula that you would recommend?
Thanks,
Caroline
Hi! The XLOOKUP formula cannot generate any value. It can only find an existing value based on the criteria. To calculate the average value, use these guidelines: How to calculate average in Excel: formula examples, Excel AVERAGEIF function to average cells with condition
Hi
I try to use average with xlookup multiple criteria.
I used Boolean logic to find the first occurrence. and then required average of the previous 60 rows.
than, I used address and cell function to convert values to cell address. than i understand that the outcome is a string so I applied indirect in order to convert to a cell reference an average function can use. unluckily for me the indirect gave value instead of cell address.
it looks something like this:
AVERAGE(INDIRECT(ADDRESS(CELL("row",XLOOKUP(1,(($A:$A>(N300+1/4))*($A:$A1)+(H:H>1)),$B:$B,"-",1,1))-60,CELL("col",XLOOKUP(1,(($A:$A>(N300+1/4))*($A:$A1)+(H:H>1)),$B:$B,"-",1,1)),4,1,))&":"&ADDRESS(CELL("row",XLOOKUP(1,(($A:$A>(N300+1/4))*($A:$A1)+(H:H>1)),$B:$B,"-",1,1)),CELL("col",XLOOKUP(1,(($A:$A>(N300+1/4))*($A:$A1)+(H:H>1)),$B:$B,"-",1,1))))+AVERAGE(INDIRECT(ADDRESS(CELL("row",XLOOKUP(1,(($A:$A>(N300+1/4))*($A:$A1)+(H:H>1)),$B:$B,"-",1,1))-60,CELL("col",XLOOKUP(1,(($A:$A>(N300+1/4))*($A:$A1)+(H:H>1)),$B:$B,"-",1,1)),4,1,)&":"&ADDRESS(CELL("row",XLOOKUP(1,(($A:$A>(N300+1/4))*($A:$A1)+(H:H>1)),$B:$B,"-",1,1)),CELL("col",XLOOKUP(1,(($A:$A>(N300+1/4))*($A:$A1)+(H:H>1)),$B:$B,"-",1,1)))))
any help will be very much appreciated
Hi! It is very difficult to understand a formula that contains unique references to your workbook worksheets. Please clarify your specific problem or provide additional information to understand what you need.
hi Alexander
thanks for your quick response
I'll do my best trying to describe my woorkbook
column A - minutes from 04/01/2022 00:00 until now range from 3 to 650002
column B - Mwt by minute based on column A range from 3 to 650002
column G - is DNI by minute based on column A range from 3 to 650002
column H - is DNI by minute based on column A range from 3 to 650002
column N - Dates by day from 04/01/2022 00:00 until now range from 3 to 452
I would like to get an output as follows: by giving date (column N) find the first time DNI by columns G&H is bigger than 1 and do average by column B and last 60 minutes from first event.
in my previous post you to averages because i need to same output from column D
hope hearing form you soon
Hype
Hi! Here is an example of a formula that might be helpful. The MATCH function finds a position number in the range where the date = N1 and the value in column H is greater than 1 (for example, we get a position number 13). Add 2 to this position number to get the row number. Use the INDIRECT function to get the address of the cell in column B (for example, B15). The function OFFSET returns the range of values from this cell and 5 rows higher (for example B11:B15). Use the AVERAGE function to calculate the average in this range.
=AVERAGE(OFFSET(INDIRECT("B"&MATCH(1,($N$3:$N$20=N1)*(H3:H20>1),0)+2),0,0,-5,1))
I hope my advice will help you solve your task.
Thank you for this very informative article. I am trying to use xlookup to return the smallest of values in the return range where the lookup value occurs multiple times in the lookup range. It looks like it just returns the first one it finds. Is there a way to use the xlookup function as is, or to nest another formula to control which value is returned?
Example:
=XLOOKUP("A-B-C-D",A:A,B:B)
Tag | Sequence
A-B-C-D | 5
C-D-T-G | 4
G-H-E-H | 2
A-B-C-D | 3
B-T-R-D | 1
"A-B-C-D" occurs twice, but would like to return the smaller of the two value (3), instead of (5) as it currently does, without having to change the sort of the table.
Thank you,
-Andres
Hi!
To search by multiple criteria, use the INDEX MATCH functions. You can learn more about MINIFS function in Excel in this article on our blog.
Try this formula:
=INDEX(B1:B5,MATCH(1,(A1:A5="A-B-C-D") * (B1:B5=MINIFS(B1:B5,A1:A5,"A-B-C-D")),0))
Good day,
I'm usinng a list with 28 data in and using xloopup to look for some values.
=$E$2&"-"&C4 This formula is used in 53 rows in column A and this combine the number in the list and number un column C.
The Xlookup is working fine with the first 103 rows that I search, but after that I get the #N/A error.
My question is how big can the Xlookup array be?
=XLOOKUP(A4,Data2!$A$2:$A$1000,Data2!$G$2:$G$1000)
Hi! The XLOOKUP function has no limitations. Error #N/A means that the data is not found. Possible causes are given in the article above.