Range Lookup Index

[kuldeepjainesl]

Hi Experts,


I am using

=COUNTIFS($A$2:$A$2278,"<="&C2,$B$2:$B$2278,">="&C2) to get the number of count and working fine.  Now if the count are more then 1, in that case we need to check the ranges for their sanity as count should not be more than 1 for ideal dataset.  


I need to know the row numbers of the [code]True

criteria for which i am getting more then 1 count.


Example dataset

[pre]ABC0000 ABC0999
ABC1000 ABC1999
ABC2000 ABC2999
ABC3000 ABC3999
ABC1000 ABC4999[/code][/pre]

Now ABC1100 value will give me a count 2 as it is true for Line 2 and Line 4. i need to get a formula based approach to get these line number as we have a large database.


Edit: This list cannot be a sorted list.

Regards,

 

[NARAYANK991]

Hi Kuldeep ,


Try this :


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


Enter this as an array formula , using CTRL SHIFT ENTER ; copy this down , to get the successive row numbers where the match occurs.


Narayan

 

[kuldeepjainesl]

Thanks Narayan,


That worked to my requirement. I am using the given version of your formula.

=IFERROR(SMALL(IF((($C2>=($A$1:$A$2277))*($C2<=($B$1:$B$2277))),ROW($A$1:$A$2277)),ROW($A$1)),"-")


with CTRL+SHIFT+ENTER

Regards,