The Excel XLOOKUP function is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP supports approximate and exact matching, wildcards (* ?) for partial matches, and lookups in vertical or horizontal ranges.Read more
In this example, we have a simple worksheet that uses the XLOOKUP function to lookup the name of a U.S. state when a valid two-letter code is provided as a lookup value. The goal is to remove the #N/A error that XLOOKUP returns when it can't find a result. Although you could use the IFNA or...Read more
In this example, the goal is to lookup a price using XLOOKUP with multiple criteria. To be more specific, we want to lookup a price based on Item, Size, and Color. At a glance, this seems like a difficult problem because XLOOKUP only has only one value for lookup_value and ...Read more
In this example, the goal is to perform a case-sensitive lookup on the color in the "Color" column. In other words, a lookup value of "RED" must return a different result from a lookup value of "Red". By default, Excel is not case-sensitive and this applies to standard lookup formulas...Read more
XLOOKUP can handle arrays natively, which makes it a very useful function when constructing criteria based on multiple logical expressions.
In the example shown, we are looking for the order number of the first order to Chicago over $250. We are constructing a lookup...Read more
When XLOOKUP can't find a value in a lookup array, it returns an #N/A error. You can use the IFNA function or IFERROR function to trap this error and return a different...Read more
In the example shown, the table in B4:C13 contains quantity-based discounts. As the quantity increases, the discount also increases. The table in E4:F10 shows the discount returned by XLOOKUP for several random quantities. XLOOKUP is configured to use the quantity in column E to find the...Read more
One nice benefit of the XLOOKUP function is that the syntax used for horizontal lookups is the same as for vertical lookups.
In the example shown, the data in C4:F5 contains quantity-based discounts. As the quantity increases, the discount also increases. The table to the right shows...Read more
In this example, the goal is to use XLOOKUP to find the first "red" or "pink" record in the data as shown. All data is in an Excel Table named data in the range B5:E14. This means the formulas below use structured...Read more
In the example shown, cell G4 contains the lookup value, "Berlin". XLOOKUP is configured to find this value in the table, and return the population. The formula in G5 is:
=XLOOKUP(G4,B5:B18,D5:D18) // get population
In this example, we have a table that contains 6 columns of codes, and each row of codes belongs to a group in column B. The goal is to lookup any code in C5:H15, and return the name of the group the code belongs to. The challenge is that the code may be in any one of the six columns, and...Read more
Normally, the XLOOKUP function is configured to look for a value in a lookup array that exists on the worksheet. However, when the criteria used to match a value becomes more complex, you can use Boolean logic to create a lookup array on-the-fly composed...Read more
The XLOOKUP function contains built-in support for wildcards, but this feature must be enabled explicitly by setting match mode to the number 2.
In the example shown, XLOOKUP is configured to match the value entered in cell E5, which may appear...Read more
By default, XLOOKUP will return the first match in a data set. However, XLOOKUP offers an optional argument called search_mode to control the order in which data is searched. Setting search mode to -1 causes XLOOKUP to search "last to first" or, in other words, search backwards through...Read more
One of the nice benefits of XLOOKUP is it can easily return entire rows or columns as a lookup result. This can be done also with INDEX and MATCH, but the syntax is more complex.
In the example shown, we want to retrieve all values associated with...Read more
XLOOKUP offers several features that make it exceptionally good for more complicated lookups. In this example, we want the latest price for an item by date. If data were sorted by date in ascending order, this would be very straightforward....Read more
This formula is based on the XLOOKUP function. Working from the inside out, we use the MAX function to calculate a lookup value:
MAX(values)
MAX is...Read more
In this example, the goal is to lookup the first negative value in a set of data. In addition, we also want to get the corresponding date. All data is in an Excel Table called data, in the range B5:C16. This information...Read more
In this example, the goal is to look up and retrieve an entire row of values in a set of data. For example, when a value like "Neptune" is entered into cell H5, all values in the range C11:F11 should be returned. For convenience and readability, project (B5:B16) and data...Read more
In this example, the goal is to lookup a number with a certain amount of allowed tolerance, defined as n. In other words, with a given lookup number we are trying to find a number in a set of data that is ± n. In the worksheet shown, the number to find is in...Read more
In this example, the goal is to look up and retrieve an entire column of values in a set of data. For example, when a value like "Q3" is entered into cell H4, all values in the range E5:E16 should be returned. For convenience and readability, quarter (C4:F4) and ...Read more
In this example, the goal is to lookup data to the left of an ID that appears as the last column in the table. In other words, we need to locate a match in column E, then retrieve a value from a column to the left. This is one of those problems that is...Read more