Returning a Value If Number Between Values In Two Columns

Question

I'm working in Google Sheets and am trying to find a more elegant way to return a value if a number in a referenced cell falls between two other values in the table. For example:

In this case elsewhere I would like to show the return value based on the value from another cell. Such as:

I could do a long IF statement but would prefer something that I can expand more readily if we update the columns.

Answer 1

I would recommend to use BYROW() for dynamic spill result. Try-

=BYROW(D2:INDEX(D2:D,COUNTA(D2:D)),LAMBDA(x,XLOOKUP(x,B2:B,C2:C,"",1)))

Normally you can use XLOOKUP() like-

=XLOOKUP(D2,$B$2:$B,$C$2:$C,"",1)

• Here D2:INDEX(D2:D,COUNTA(D2:D)) will return a array of values as well cell reference from D2 to last non empty cell in column D (Assume you do not have any blank rows inside data). If you have blank row, then you have to use different approach. See this post by @TheMaster

• Then LAMBDA() will apply XLOOKUP() function for each cell of D column.

Answer 2

*LOOKUP functions with sorted arrays allow you to do exactly this.

Here's a possible solution:

=ARRAYFORMULA(LOOKUP(E2:E5,A2:A6,C2:C6))

You only need the lower-bounds (A2:A6), the upper-bounds (B2:B6) are assumed by the function.

Answer 3

Don't know if this is elegant enough to you or not, but using a LAMBDA function do always help improve the readability of codes in excel and google sheet.

This sample code uses...

QUERY, to remove the empty array for the output result.

BYROW, to iterate the input array and work on them one by one.

LAMBDA, to name the hard-to-read parts in the formula,

FILTER, to compare the input data with the MIN/MAX value and get the RETURN column

INDEX, to simplify the required inputs, instead of giving the formula A:A, B:B, C:C, here you only heed to give a range of A1:C, and the formula will seprerate it into MIN, MAX and RETURN column on its own.

This is also expandabale as you requested, since the input ranges are A1:C and E2:E, you may add as many other MIN/MAX/RETURN reference and TEST CASES as you want.

=QUERY(LAMBDA(REF,CASES,
 BYROW(CASES,LAMBDA(CASE,
  LAMBDA(MIN,MAX,RETURN,
   IF(CASE="","",FILTER(RETURN,CASE>=MIN,CASE<=MAX))
  )(INDEX(REF,,1),INDEX(REF,,2),INDEX(REF,,3))
 ))
)($A$1:$C$,$E$2:E),"WHERE Col1 IS NOT NULL")

edit: should use >= and <= in the comparings, otherwises the perfect matches may be ignored

Answer 4

use:

=INDEX(VLOOKUP(E2:E5; A2:C; 3))

like this:

=INDEX(IF(E2:E="";;IFNA(VLOOKUP(E2:E; A2:C; 3))))