# Checking to see if a number falls within a range

I have a Google sheet that has two tabs and I am trying to build something that takes column B from the Sheet2 tab and cross-references Sheet 1 and highlights or lists on Sheet2 the ones that fall in the range on Sheet1. i.e. Sheet2 column B cross-referenced against Sheet1 column H

Test Sheet Sample

• The dataset in the sample spreadsheet is pretty large. You may want to cut it down to say 100 representative sample data rows, and show your manually entered desired results from that smaller dataset. Commented Jul 19 at 14:16

Put this formula in cell Sheet2!J2:

=let(
keys, B2:B,
ranges, sort(Sheet1!F2:G),
candidates, arrayformula(ifna(vlookup(
keys, ranges, { 1, 2 }, true
))),
lows, choosecols(candidates, 1),
highs, choosecols(candidates, 2),
map(keys, lows, highs, lambda(k, l, h,
if(len(k) * isbetween(k, l, h),
l & "-" & h,
iferror(ø)
)
))
)


The formula will show the range that the ID in that row falls into, or a blank when there is no range in Sheet1!F2:G that matches.

There are 1,339 ranges in Sheet1!F2:G that seem incorrect. The lower bound of those ranges is greater than the higher bound. The first such incorrect range is in Sheet1!F47:G47. You can list all such ranges like this:

=filter(Sheet1!H2:H, Sheet1!F2:F > Sheet1!G2:G)