How to search within a range of numbers in a cell in Excel?-CodePudding

I have a column on my worksheet that is supposed to be ranges of numbers like this (Random Digit Assignment).

Each cell are created by the ROW formula.

For Example

(E4): =ROWS(D4:D4)(D41000)&" - "&ROWS(D5:D5)(D51000) (but it does not matter)

Now, what I want is to search numbers within those ranges in cells. Like I want to search 210 and the result that I want is 5

Is that possible? How?

CodePudding user response：

You could use:

=MATCH(210,--LEFT(E3:E18,FIND(" ",E3:E18)))

If you don't have ms365 and you don't want to CSE this formula use:

=MATCH(210,INDEX(--LEFT(E3:E18,FIND(" ",E3:E18)),))

CodePudding user response：

You can use the MATCH function in general for that purpose. If you had a separate column for start and end of range, then you could use two MATCH calls to find the closest start/end of range and then return the address of the lookup result. You can compare the closest start and closest end with the searched term and choose the row of the one which is closer. From there on, the final step is to just get the proper cell in the row you found.