Finding values in a range of from/to arrays

HuRocan

New member
Joined
Jan 27, 2020
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2013
Hi all,

I am new to this forum, so a bit unsure how this would work. Anyway, I have a situation where in a workbook, in one sheet, I have a list of random numbers, sorted ascending (please see example sheet attached, "data" worksheet), and in another sheet ("ranges") I have a from / to set of ranges of numbers. I need to find if a particular number in the "data" worksheet is found in any of the from/to ranges in the "ranges" worksheet. If the number is found in a range, then I'd like to know the row number of that range, input in column B of the "data" worksheet against that number. If it is not found in any of the from/to ranges, then nothing should be displayed. Any help, even if it involves VBA, would be very appreciated.

Many thanks.
 

Attachments

  • example.xlsx
    10.5 KB · Views: 7
Last edited:
worksheet "data"

B2=MATCH(LOOKUP(data!A2,ranges!$A$2:$A$18),ranges!A:A,)
 
Thanks caabyyc, but it doesn't work properly. Example is 50041 or 50042, neither of those is found in the from/to columns in the ranges! worksheet. This is because it skips between 50010 and 50050 if you look closely. The formula proposed looks for the lower value between the looked-up value and the ranges I have defined, can't identify that the value looked up actually does not exist in the ranges proposed.
 
I note that there are gaps between some of your ranges and that none overlap, so a simple formula in B2 of the data sheet:
Code:
=SUMPRODUCT(($A2>=ranges!$A$2:$A$18)*($A2<=ranges!$B$2:$B$18)*ROW(ranges!$A$2:$A$18))
will give you the row number, but will return 0 (zero) if not found. You could format the cells to hide zeroes.

A tweak to the above will show blanks instead of zeroes:
Code:
=IFERROR(1/(1/SUMPRODUCT(($A2>=ranges!$A$2:$A$18)*($A2<=ranges!$B$2:$B$18)*ROW(ranges!$A$2:$A$18))),"")

However, should some of your ranges overlap and a value can be found in two or more ranges, that needs this formula to be array-entered (using CTRL+SHIFT+ENTER to commit the formula to the sheet, rather than just ENTER) in cell B2 and then copied down:
Code:
=TEXTJOIN(", ",TRUE,IF(($A2>=ranges!$A$2:$A$19)*($A2<=ranges!$B$2:$B$19)>0,ROW(ranges!$A$2:$A$19),""))
The cell will show a list of rows separated by commas.

In all cases, the ranges on the ranges sheet do not need to be sorted.
 
Last edited:
Yes, your are right, sorry for that.

(revised) B2=IFERROR(LOOKUP(1,0/((A2>=ranges!$A$2:$A$18)*(A2<=ranges!$B$2:$B$18)),ROW(ranges!$A$2:$A$18)),"")



Thanks caabyyc, but it doesn't work properly. Example is 50041 or 50042, neither of those is found in the from/to columns in the ranges! worksheet. This is because it skips between 50010 and 50050 if you look closely. The formula proposed looks for the lower value between the looked-up value and the ranges I have defined, can't identify that the value looked up actually does not exist in the ranges proposed.
 
My ranges do not overlap, and this seems to be working, thank you very much for your help.
 
Any suggestion as to how to do this with VBA? Given the reason that if there is a lot of data, like over 100,000 lines of data in the ranges! sheet, the calculations via Excel formula take forever...

Many thanks.
 
Specifically for the file you attached:
Code:
Sub blah()
With Sheets("Data").Range("B2:B16")

'either: one of these three lines:
'  .FormulaR1C1 = "=SUMPRODUCT((RC1>=ranges!R2C1:R18C1)*(RC1<=ranges!R2C2:R18C2)*ROW(ranges!R2C1:R18C1))"
  .FormulaR1C1 = "=IFERROR(1/(1/SUMPRODUCT((RC1>=ranges!R2C1:R18C1)*(RC1<=ranges!R2C2:R18C2)*ROW(ranges!R2C1:R18C1))),"""")"
'  .FormulaR1C1 = "=IFERROR(LOOKUP(1,0/((RC[-1]>=ranges!R2C1:R18C1)*(RC[-1]<=ranges!R2C2:R18C2)),ROW(ranges!R2C1:R18C1)),"""")"

'or: these two lines for overlapping ranges:
'  .Cells(1).FormulaArray = "=TEXTJOIN("", "",TRUE,IF((RC1>=ranges!R2C1:R19C1)*(RC1<=ranges!R2C2:R19C2)>0,ROW(ranges!R2C1:R19C1),""""))"
'  .Cells(1).AutoFill Destination:=.Cells, Type:=xlFillDefault

'convert to values:
  .Value = .Value
End With
End Sub
but it writes the formula into the cells and then converts them to plain values, so it may still take ages.
Supply a file with lots of data (link to it on a file sharing site if it's to big to be attached here) and I'll try to write vba which could be faster.
 
Back
Top