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

[Richard Radcliffe]

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.

Thanks in advance,
Richard

 

[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. 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.