Skip to main content
  • Login
Cart
Exceljet logo Exceljet
  • Training
  • Videos
  • Functions
  • Formulas
  • Shortcuts
  • Articles
Cart
  • Login
  • Training
  • Videos
  • Functions
  • Formulas
  • Shortcuts
  • Articles
  • Login

Search results

Function

XLOOKUP Function

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

Formula

XLOOKUP without #N/A error

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

Formula

XLOOKUP with multiple criteria

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

Formula

XLOOKUP case-sensitive

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

Formula

XLOOKUP with logical criteria

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

Formula

XLOOKUP return blank if blank

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

Formula

XLOOKUP basic approximate match

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

Formula

XLOOKUP horizontal lookup

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

Formula

XLOOKUP with Boolean OR logic

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

Formula

XLOOKUP basic exact match

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
  • The
  • ...Read more
Video

Basic XLOOKUP example

In this video, we’ll set up the XLOOKUP function with a basic example. Matching on City name, we'll retrieve Country and Population...Read more
Formula

XLOOKUP match any column

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

Formula

XLOOKUP two-way exact match

One of XLOOKUP's features is the ability to lookup and return an entire row or column. This feature can be used to nest one XLOOKUP inside another to perform a two-way lookup. The inner XLOOKUP returns a result to the outer XLOOKUP, which returns a final result...Read more

Formula

XLOOKUP wildcard match example

Working from the inside out, XLOOKUP is configured to find the value in H4 in the Last name column, and return all fields. In order to support wildcards, match_mode is provided as 2:

XLOOKUP(H4,D5:D15,B5:E15,2) //...Read more
Formula

XLOOKUP with complex multiple criteria

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

Formula

XLOOKUP match text contains

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

Video

Basic XLOOKUP approximate match

In this video, we’ll set up the XLOOKUP function to perform an approximate match in order to calculate a quantity based discount.Read more
Formula

XLOOKUP rearrange columns

This formula uses XLOOKUP twice, by nesting one XLOOKUP inside another. The first (inner) XLOOKUP is used to perform an exact match lookup on the value in G5:

XLOOKUP(G5,E5:E15,B5:E15)
  • The
  • ...Read more
Video

XLOOKUP with multiple lookup values

In this video, we'll set up XLOOKUP to return multiple values in a dynamic array, by providing a range of lookup values instead of a single lookup value...Read more
Formula

XLOOKUP last match

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

Formula

XLOOKUP lookup row or column

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

Video

XLOOKUP with boolean logic

In this video we'll look how to use the XLOOKUP function with Boolean logic to apply multiple criteria. ...Read more
Formula

XLOOKUP latest by date

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

Formula

XLOOKUP lookup left

Whereas VLOOKUP is limited to lookups to the right of the lookup column, XLOOKUP can lookup values to the left natively. This means XLOOKUP can be used instead of INDEX and...Read more

Formula

XLOOKUP date of max value

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

Formula

Lookup first negative value

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

Formula

Look up entire row

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

Formula

Lookup number plus or minus N

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

Formula

Look up entire column

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

Formula

Left lookup with INDEX and MATCH

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

Pages

  • 1
  • 2
  • 3
  • next ›
  • last »

Topics

  • Formula Basics
  • Formula Examples
  • Conditional Formatting
  • Pivot Tables
  • Excel Tables
  • Excel Charts
  • Shortcuts
  • More...

Key Functions

  • IF function
  • VLOOKUP function
  • XLOOKUP function
  • FILTER function
  • SUMIFS function
  • COUNTIFS function
  • SUMPRODUCT function
  • INDEX and MATCH
  • 350 Excel Functions
Exceljet logo Exceljet
Quick, clean, and to the point

Resources

  • Training
  • Videos
  • Functions
  • Formulas
  • Shortcuts
  • Articles

About Us

  • About
  • Testimonials
  • Topics
  • Contact

Newsletter Sign-up

Work faster in Excel. Join more than 100,000 people who get weekly tips from us.
© 2012-2023 Exceljet. Terms of use
  • Visit our Twitter page