• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup between range in case of multiple True

kuldeepjainesl

kuldeepjainesl

Member
Hi Experts,


After following your very useful post on Range Lookup here: (http://chandoo.org/wp/2010/06/30/range-lookup-excel/)


I stuck up when my value is matching for two ranges. If the value in found in two range it give the answer as "Not found". I wish to have all the row numbers for which it is true.


Example


Start-------End

10----------25

30----------45

30----------150

300---------450

500---------501

502---------600


Now I searched "33" which is true for row 2,3


Regards,
 
Try the following formula, this one assumes that the Columns are A and B and the Data is from A4 to B4, the search value is in C7.


This is an array formula that require you to hit Ctr, Shift and Enter.


=(IF(--($A$1:$A$4>=$C$7)*($B$1:$B$4<=$C$7),ROW($A$1:$A$4),""))
 
HI Kuldeep,


Kahiba's formula is correct but it needs a little modification, It should be changed to give you the multiple returns for a match type. Examine this formula. Your data including headers are present in Cell A1:B7 like bellow and you have put this formula as array formula Ctrl+Shift+Enter

[pre]
Code: 
Start	End	33
10	25	1
30	45	2
30	150	3
300	450	4
500	501	5
502	600	6[/pre]

Put in D2 this formula and drag down:

=IFERROR(SMALL(IF((($C$1>=($A$2:$A$7))*($C$1<=($B$2:$B$7))),ROW($A$2:$A$7)),ROW(A1)),"-")


Hope that works!


Faseeh
 
Hi Faseeh,


I am using you gem from last one month and never failed. However from last one month I tried few time to understand how it works but could not.


=IFERROR(SMALL(IF((($C$1>=($A$2:$A$7))*($C$1<=($B$2:$B$7))),ROW($A$2:$A$7)),ROW(A1)),"-")


May any of our expert will explain this to me. I get lost after coming out from "if" function.


Regards,
 
Hi Kuldeep ,


The IF statement is the only part of the above formula which you need to understand !


The IFERROR is not an essential part of the formula , in the sense that it is not the part which makes it work. We are left with :


SMALL(IF((($C$1>=($A$2:$A$7))*($C$1<=($B$2:$B$7))),ROW($A$2:$A$7)),ROW(A1))


You know that the SMALL function merely returns the 1st , 2nd , 3rd ,... smallest value from the array , which in this case is just the array {2;3;4;5;6;7} provided the IF condition is satisfied i.e. if the IF condition returns an array {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE} , the output of the IF statement will be {2;3;4;5;6;7} ; if any of the elements is not TRUE , then the IF condition can return an array such as {TRUE;FALSE;FALSE;TRUE;TRUE;FALSE}. In this case , the output of the IF statement will be {2;FALSE;FALSE;5;6;FALSE}


The smallest value from this array will be 2 , the next will be 5 , and the next will be 6.


The basic mechanism is the IF condition :


($C$1>=($A$2:$A$7))*($C$1<=($B$2:$B$7))


What this is doing is checking whether the value in C1 is between the values in the array A2:A7 and the values in the array B2:B7 ; the result will be an array of TRUE / FALSE values , TRUE where the value in C1 is between the values in A2:A7 and B2:B7 , and FALSE otherwise. Given that C1 contains 33 , the array will be {FALSE;TRUE;TRUE;FALSE;FALSE;FALSE}.


Narayan
 
You must log in or register to reply here.
Back
Top