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


R

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
 
Ad

Advertisements

L

Luke M

Something like this maybe?

=INDEX(D: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.
 
Ad

Advertisements

T

T. Valko

In this example, I would get back:
=INDEX(D: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: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: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:D$4,4,0),#N/A)

Or this array** version:

=INDEX(D$2:D$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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top