If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lookup value that falls between two values in a range and then som
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 |
#2
|
|||
|
|||
Lookup value that falls between two values in a range and then som
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. -- Best Regards, Luke M "Richard Radcliffe" Richard wrote in message ... 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 |
#3
|
|||
|
|||
Lookup value that falls between two values in a range and then som
In this example, I would get back:
#N/A ID2 #N/A ID3 =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. -- Biff Microsoft Excel MVP "Luke M" wrote in message ... 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. -- Best Regards, Luke M "Richard Radcliffe" Richard wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|