Return a result between 2 values

Last Updated on 14/05/2024
Reading time: 3 minutes

You can return a result between 2 values in Excel with 2 functions

  1. Don't use the IF function ⛔

    The construction of Nested IFs is way too long and a source of mistakes

  2. The VLOOKUP function

    This function does the job but is limited (sorted data, only one way to search)

  3. The XLOOKUP function

    XLOOKUP function can also calculate between 2 values but it can search from first to last or last to first.

Don't use the IF function ⛔

This is how most of the Excel users try to solve this problem. It works but it's not the most efficient solution. With the IF function, we have to consider each case in a specific IF. For instance, for cell C2, we can write several nested IFs.

=IF(B2>=30,F8,IF(B2>=25,F7,IF(B2>=20,F6,IF(B2>=10,F5,F4))))

Create IF nested is complex with risk of mistake

Yes it works, and the result is correct but this formula is horrible to write. And there is a lot of chance to make mistakes.

Solution #1: Search in a range of values with VLOOKUP

For years, the only Excel function that returned the result in a range of values was VLOOKUP with the last argument = TRUE (or 1)

  • lookup_value: The value you want to search for.
  • table_array: The range of cells that contains the data.
  • col_index_num: The column number in the table_array from which to retrieve the value.
  • range_lookup: TRUE for an approximate match.
VLOOKUP returns the result for any values

But, the VLOOKUP function has 2 limitations

  • Your data MUST be sorted in the lookup table
  • The direction of the research is always in increasing order

Solution #2: Search in a range of values with XLOOKUP

Microsoft engineers have created the XLOOKUP function to correct the limitations of the VLOOKUP function. Let's redo the same example and see how to write the XLOOKUP function in this situation

  1. Write the name =XLOOKUP
  2. Select the cell with the value to search (here B2)
  3. Then you select only the column with the range of values ($E$3:$E$8)
  4. And after, the column of the values to return ($F$3:$F$8)
  5. Skip the next argument (not necessary in this example)
  6. Select the option of research "exact match or next smaller item" (value -1)
Option Exact Match or next smaller item

The complete formula is:

Search in a range of values with the XLOOKUP function

If the data in the lookup table is not sorted, it works 😀✌️

Search in a Range of values unsorted

XLOOKUP manages both directions 🥳

  • With VLOOKUP, you can only return a result when a threshold is reached.
  • But with XLOOKUP, you can also return the result in both directions.
  • In this example, you want to calculate the price according to the number of people for an event.
Calculate the price for the event

To calculate the rental price of the tent, we will use

  • The number of people (B3)
  • The column with the different steps (D5:D9)
  • The column with the price for each threshold
  • The formula is

=XLOOKUP(B3,Tbl_Tent[Number people],Tbl_Tent[Price],,1)

The result returned is the upper value of the range [50-75]. Only XLOOKUP can be used in this situation.

Price for the tent according to the number of people

In the same way, the formula to calculate the price of the food for 60 people is:

Price for the food according to the number of people

Leave a Reply

Your email address will not be published. Required fields are marked *

Return a result between 2 values

Reading time: 3 minutes
Last Updated on 14/05/2024

You can return a result between 2 values in Excel with 2 functions

  1. Don't use the IF function ⛔

    The construction of Nested IFs is way too long and a source of mistakes

  2. The VLOOKUP function

    This function does the job but is limited (sorted data, only one way to search)

  3. The XLOOKUP function

    XLOOKUP function can also calculate between 2 values but it can search from first to last or last to first.

Don't use the IF function ⛔

This is how most of the Excel users try to solve this problem. It works but it's not the most efficient solution. With the IF function, we have to consider each case in a specific IF. For instance, for cell C2, we can write several nested IFs.

=IF(B2>=30,F8,IF(B2>=25,F7,IF(B2>=20,F6,IF(B2>=10,F5,F4))))

Create IF nested is complex with risk of mistake

Yes it works, and the result is correct but this formula is horrible to write. And there is a lot of chance to make mistakes.

Solution #1: Search in a range of values with VLOOKUP

For years, the only Excel function that returned the result in a range of values was VLOOKUP with the last argument = TRUE (or 1)

  • lookup_value: The value you want to search for.
  • table_array: The range of cells that contains the data.
  • col_index_num: The column number in the table_array from which to retrieve the value.
  • range_lookup: TRUE for an approximate match.
VLOOKUP returns the result for any values

But, the VLOOKUP function has 2 limitations

  • Your data MUST be sorted in the lookup table
  • The direction of the research is always in increasing order

Solution #2: Search in a range of values with XLOOKUP

Microsoft engineers have created the XLOOKUP function to correct the limitations of the VLOOKUP function. Let's redo the same example and see how to write the XLOOKUP function in this situation

  1. Write the name =XLOOKUP
  2. Select the cell with the value to search (here B2)
  3. Then you select only the column with the range of values ($E$3:$E$8)
  4. And after, the column of the values to return ($F$3:$F$8)
  5. Skip the next argument (not necessary in this example)
  6. Select the option of research "exact match or next smaller item" (value -1)
Option Exact Match or next smaller item

The complete formula is:

Search in a range of values with the XLOOKUP function

If the data in the lookup table is not sorted, it works 😀✌️

Search in a Range of values unsorted

XLOOKUP manages both directions 🥳

  • With VLOOKUP, you can only return a result when a threshold is reached.
  • But with XLOOKUP, you can also return the result in both directions.
  • In this example, you want to calculate the price according to the number of people for an event.
Calculate the price for the event

To calculate the rental price of the tent, we will use

  • The number of people (B3)
  • The column with the different steps (D5:D9)
  • The column with the price for each threshold
  • The formula is

=XLOOKUP(B3,Tbl_Tent[Number people],Tbl_Tent[Price],,1)

The result returned is the upper value of the range [50-75]. Only XLOOKUP can be used in this situation.

Price for the tent according to the number of people

In the same way, the formula to calculate the price of the food for 60 people is:

Price for the food according to the number of people

Leave a Reply

Your email address will not be published. Required fields are marked *