Formula to Find if Number is between a Range in 2 Columns and then Pick 1st Column, or 2nd if Empty-CodePudding

There are 4 columns. 1st column has the result, 2nd column has a backup result if the 1st column is empty, the 3rd column has the low/floor value of range, and the 4th column has the high/ceiling value of range.

The Excel formula should check and see what row the search value sits in between columns 3 and 4, and then pulls column 1 if a value is found, or pulls column 2.

| 1st Column |  2nd Column|3rd Column ||3rd Column |
|------------|------------|-----------|------------|
| a          |az1         |    1      |        5   |
| b          |az2         |    6      |        10   |
| c          |az3         |    11     |        15   |
| -           |az4         |    16     |        20   |

Search Value 1: 13
Result: c

Search Value 2: 6
Result: b

Search Value 3: 19
Result: az4

Thank you in Advance for help and guidance!!

CodePudding user response：

One way with Office 365 is:

=LET( x, G2,
      low, $A$1:$B$4,
      high, $C$1:$C$4,
        t, INDEX(low, MATCH(x,high,1),),
        INDEX( t, 1, IF(ISBLANK(INDEX(t,1,1)),2,1) ) )

You can put an IFERROR in it if you want it to give the "No Scores" result.

CodePudding user response：

Try this formula for the all Excel version.

In G2, enter array (CSE) formula :

=LOOKUP(F2,C2:C5,IF(A2:A5="-",B2:B5,A2:A5))

Based on your Excel version, the above is an array (CSE) formula to be confirmed by pressing "Ctrl Shift Enter" to entry.