Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

Excel XLOOKUP Between 2 Values in 4 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)

By J.A. Gomez J.A. Gomez from Power Spreadsheets

In this Excel XLOOKUP Between 2 Values Tutorial, you learn how to create an Excel XLOOKUP between 2 values formula.

The XLOOKUP function is available in Excel 2021 and later (including Excel 365).

This Excel XLOOKUP Between 2 Values Tutorial is accompanied by an Excel workbook with the data and formulas I use. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel XLOOKUP between 2 values example workbook

Related Excel Training Materials and Resources

The following Excel VLookup Tutorials may help you better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

This Excel XLOOKUP Between 2 Values Tutorial is part of a more comprehensive series of Excel XLOOKUP Tutorials.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials and Training Resources:

If you want to learn more about Excel essentials, Excel formulas, and similar Excel topics, you may be interested in taking one (or more) Excel Courses: Click here to learn more about these Excel Courses (affiliate link).

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

If you need help with Excel tasks/projects, you may be interested in working with me: Click here to learn more about working with me.


Get immediate free access to the Excel XLOOKUP between 2 values example workbook

Excel XLOOKUP Between 2 Values Formula Template/Structure

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-between-values/
=XLOOKUP(1,(ColumnOrRowWithValues>=LowerBoundaryValue)*(ColumnOrRowWithValues<=UpperBoundaryValue),ColumnsOrRowsWithValuesToReturn)


Get immediate free access to the Excel XLOOKUP between 2 values example workbook

Step-by-Step Process to Create an Excel XLOOKUP Between 2 Values Formula

(1) Call the XLOOKUP function.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-between-values/
=XLOOKUP(...)

(2) Set the first argument of the XLOOKUP function (lookup_value) to 1. This is the value your search for (the lookup value) in the array of 0s and 1s you create in step #3.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-between-values/
=XLOOKUP(1,...)

(3) Use the second argument of the XLOOKUP function (lookup_array) to specify the 2 values your XLOOKUP between 2 values formula considers.

To achieve this, do the following:

(3.1) Test whether the values in the column (when doing a VLookup) or row (when doing an HLookup) where you search are greater than or equal to (>=) the lower boundary value your XLOOKUP between 2 values formula considers.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-between-values/
=XLOOKUP(1,(ColumnOrRowWithValues>=LowerBoundaryValue)...)

As a general rule: Specify the column (when doing a VLookup) or row (when doing an HLookup) where you search as:

  • A single-column (1 column, several rows) cell range, when doing a VLookup; or
  • A single-row (1 row, several columns) cell range, when doing an HLookup.

(3.2) Test whether the values in the column (when doing a VLookup) or row (when doing an HLookup) where you search are less than or equal to (<=) the upper boundary value your XLOOKUP between 2 values formula considers.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-between-values/
=XLOOKUP(1,(ColumnOrRowWithValues>=LowerBoundaryValue)...(ColumnOrRowWithValues<=UpperBoundaryValue),...)

(3.3) Multiply the results of the 2 conditional tests you carry out in steps #3.1 and #3.2.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-between-values/
=XLOOKUP(1,(ColumnOrRowWithValues>=LowerBoundaryValue)*(ColumnOrRowWithValues<=UpperBoundaryValue),...)

(4) Use the third argument of the XLOOKUP function (return_array) to specify the return array. This is the column (when doing a VLookup) or row (when doing an HLookup) with the value to return.

Ensure the number of rows (when doing a VLookup) or columns (when doing an HLookup) of the return array (you specify in this step #4) is the same as that of the column (when doing a VLookup) or row (when doing an HLookup) where you search (you specified in step #3).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-between-values/
=XLOOKUP(1,(ColumnOrRowWithValues>=LowerBoundaryValue)*(ColumnOrRowWithValues<=UpperBoundaryValue),ColumnsOrRowsWithValuesToReturn)


Get immediate free access to the Excel XLOOKUP between 2 values example workbook

How (and Why) the XLOOKUP Between 2 Values Formula Works

The XLOOKUP function:

  1. Searches a range or array for a match; and
  2. Returns the corresponding item(s) from a second range or array.

The XLOOKUP between 2 values formula template/structure you learned in this Tutorial relies on XLOOKUP's ability to search in an array. To achieve this, the XLOOKUP between 2 values formula template/structure:

  1. Sets XLOOKUP's first argument (lookup_value) to 1; and
  2. Specifies XLOOKUP's second argument (lookup_array) as an array of 0s and 1s.

The expression template/structure to specify XLOOKUP's second argument (lookup_array) when creating an XLOOKUP between 2 values formula is as follows:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-between-values/
(ColumnOrRowWithValues>=LowerBoundaryValue)*(ColumnOrRowWithValues<=UpperBoundaryValue)

This expression template/structure does 2 comparisons:

  1. (ColumnOrRowWithValues>=LowerBoundaryValue): Tests whether the values in the applicable column or row (ColumnOrRowWithValues) are greater than or equal to (>=) the lower boundary value (LowerBoundaryValue) the XLOOKUP between 2 values formula considers.
  2. (ColumnOrRowWithValues<=UpperBoundaryValue): Tests whether the values in the applicable column or row (ColumnOrRowWithValues) are less than or equal to (<=) the upper boundary value (UpperBoundaryValue) the XLOOKUP between 2 values formula considers.

Each of these 2 individual expressions returns an array of Boolean values (TRUE or FALSE). For each individual value inside the column (when doing a VLookup) or row (when doing an HLookup) where you search:

  • (ColumnOrRowWithValues>=LowerBoundaryValue) returns:
    • TRUE if the applicable value is greater than or equal to the lower boundary value.
    • FALSE if the applicable value is less than the lower boundary value.
  • (ColumnOrRowWithValues<=UpperBoundaryValue) returns:
    • TRUE if the applicable value is less than or equal to the upper boundary value.
    • FALSE if the applicable value is greater than the upper boundary value.

In other words:

  • Both expressions return TRUE if the applicable value is between the 2 values your XLOOKUP formula considers.
  • 1 or both expressions return FALSE if the applicable value isn't between the 2 values your XLOOKUP formula considers.

The multiplication operator (*):

  • Converts the Boolean values into 0s and 1s.
    • FALSE is converted to 0.
    • TRUE is converted to 1.
  • Acts as the AND logical operator. In other words:
    • If the applicable value is between the 2 values your XLOOKUP between 2 values formula considers, the multiplication operator (*):
      • Multiplies TRUE times TRUE (1*1); and
      • Returns 1.
    • If the applicable value isn't between the 2 values your XLOOKUP between 2 values formula considers, the multiplication operator (*):
      • Multiplies:
        • TRUE times FALSE (1*0); or
        • FALSE times TRUE (0*1); or
        • FALSE times FALSE (0*0); and
      • Returns 0.

In other words: The entire expression template/structure returns an array with 0s and 1s.

  • 1 indicates that the applicable value (inside the column or row where you search) is between the 2 values your XLOOKUP between 2 values formula considers.
  • 0 indicates that the applicable value (inside the column or row where you search) isn't between the 2 values your XLOOKUP between 2 values formula considers.

Since the lookup value of the XLOOKUP between 2 values formula is set to 1, the XLOOKUP function finds a match when the applicable value (inside the column or row where you search) is between the 2 values your XLOOKUP between 2 values formula considers.


Get immediate free access to the Excel XLOOKUP between 2 values example workbook

Excel XLOOKUP Between 2 Values Example Formula

This Excel XLOOKUP Between 2 Values Tutorial is accompanied by an Excel workbook with the data and formulas I use. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel XLOOKUP between 2 values example workbook

Excel XLOOKUP Between 2 Values Example Worksheet

The example worksheet has 3 tables/sections with the following characteristics:

(1) Table 1 (cells A6 to L26).

The table:

  • With data.
  • Where I search with the XLOOKUP between 2 values example formula.

Strictly speaking:

  • Columns A to H (cells A6 to H26) store the data the XLOOKUP between 2 values example formula works with; and
  • Columns I to L (cells I6 to L26) are helper columns I use for illustrative purposes.

(2) 2 values (cells N6 to O7).

The 2 values considered by the XLOOKUP between 2 values example formula are stored in cells:

  • O6 (50,000); and
  • O7 (60,000).

(3) XLOOKUP between 2 values example formula (cells N9 to O10).

  • Cell N10:
    • Stores the XLOOKUP between 2 values example formula; and
    • Displays the results.
  • Cell O10 displays the XLOOKUP between 2 values example formula I enter in cell N10.
Example worksheet for Excel XLOOKUP between 2 values

Excel XLOOKUP Between 2 Values Example Formula

The XLOOKUP between 2 values example formula stored in cell N10 is as follows:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-between-values/
=XLOOKUP(1,(H7:H26>=O6)*(H7:H26<=O7),A7:A26)

The XLOOKUP function searches:

  • For a value between:
    • 50,000 (stored in cell O6); and
    • 60,000 (stored in cell 07).
  • In column H (cells H7 to H26).

Columns I (cells I7 to I26) and J (cells J7 to J26) return TRUE or FALSE, depending on whether the value stored in column H of the applicable row:

  • Is greater than or equal to 50,000 (column J).
  • Is less than or equal to 60,000 (column K).

Column K (cells K7 to K26) returns 0 or 1, depending on whether the value stored in column H of the applicable row is between the 2 values (50,000 and 60,000) considered by the XLOOKUP between 2 values example formula. The (only) value (in column H) between these 2 values is found in row 11 (57,399.18 in cell H11).

Column L (cell L7) displays the formula stored in cell K7. This formula uses the (same) expression I use to specify the second argument (lookup_array) of the XLOOKUP between 2 values example formula.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-between-values/

'XLOOKUP between 2 values example formula
=XLOOKUP(1,(H7:H26>=O6)*(H7:H26<=O7),A7:A26)

'example formula stored in cell K7
=(H7:H26>=O6)*(H7:H26<=O7)
Array for Excel XLOOKUP between 2 values

Because of the above, the XLOOKUP function returns the value stored in row 11 of the return array (column A, cells A7 to A26). That is, the XLOOKUP between 2 values example formula returns the value stored in cell A11 (Salesperson 29).

Excel XLOOKUP between 2 values example formula


Get immediate free access to the Excel XLOOKUP between 2 values example workbook

Download the Excel XLOOKUP Between 2 Values Example Workbook

This Excel XLOOKUP Between 2 Values Tutorial is accompanied by an Excel workbook with the data and formulas I use. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel XLOOKUP between 2 values example workbook

Related Excel Training Materials and Resources

The following Excel VLookup Tutorials may help you better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

This Excel XLOOKUP Between 2 Values Tutorial is part of a more comprehensive series of Excel XLOOKUP Tutorials.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials and Training Resources:

If you want to learn more about Excel essentials, Excel formulas, and similar Excel topics, you may be interested in taking one (or more) Excel Courses: Click here to learn more about these Excel Courses (affiliate link).

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

If you need help with Excel tasks/projects, you may be interested in working with me: Click here to learn more about working with me.


Get immediate free access to the Excel XLOOKUP between 2 values example workbook

guest
1 Comment
Most Voted
Newest Oldest
Inline Feedbacks
View all comments

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA. Here are some of my most popular Excel Training Resources:

  1. Free Excel VBA Email Course
  2. Excel Macro Tutorial for Beginners
  3. Excel Power Query (Get and Transform) Tutorial for Beginners
  4. Excel Keyboard Shortcut Cheat Sheet
  5. Excel Resources
About

Contact

Terms and Conditions

Privacy Policy

Limit of Liability and Disclaimer of Warranty

Affiliate Disclosure