# Lookup value that falls between two values in a range and then som

R

I have an array with 4 columns:

A B C D
1 435 578 ID1
2 12 113 ID2
3 1478 1879 ID3
etc etc etc etc

The value I want to lookup has two components that correspond to column A
and a number that falls between columns B and C (or not):

E F
1 78
2 86
2 1500
3 1600
etc etc

So I'd like to ask is the value in column E = to the value in column A AND
does the value in column F fall between the values in columns B and C? If
yes, return column D.

In this example, I would get back:
#N/A
ID2
#N/A
ID3
etc

I hope I've explained this well.

Richard

L

#### Luke M

Something like this maybe?

=INDEX(D ,SUMPRODUCT(--(\$A\$2:\$A\$4=E2),--(\$B\$2:\$B\$4<=F2),--(\$C\$2:\$C\$4>=F2),ROW(\$A\$2:\$A\$4)))

formula evaluates to 0 if no result is found. Could encase this formula in
an IF function if "N/A" output is necessary.

T

#### T. Valko

In this example, I would get back:
=INDEX(D ,SUMPRODUCT(--(\$A\$2:\$A\$4=E2),--(\$B\$2:\$B\$4<=F2),--(\$C\$2:\$C\$4>=F2),ROW(\$A\$2:\$A\$4)))

With that formula I get these results:

ID1
ID2
ID3
ID3

When SUMPRODUCT = 0 then you get:

=INDEX(D ,0)

Which evaluates the *entire* indexed range and if (because) the formula is
entered on row 2 (cell G2) you get the result that is within the implicit
intersection of D and G2 = ID1.

Try this...

=IF(F2=MEDIAN(F2,INDEX(B\$2:C\$4,MATCH(E2,A\$2:A\$4,0),0)),VLOOKUP(E2,A\$2 \$4,4,0),#N/A)

Or this array** version:

=INDEX(D\$2 \$4,MATCH(1,IF(A\$2:A\$4=E2,IF(F2>=B\$2:B\$4,IF(F2<=C\$2:C\$4,1))),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.