Lookup and match a number to a start and end sequence column

Hi Everyone,

I have been working on solving a problem in Excel for a couple of days and wondered if there was a quicker method to achieve the below? I have tried index and match but cannot use autofill to drag down to the rest of the values I need to check. Not sure if PowerQuery would solve this.

I have a worksheet with 2 columns: Start Sequence, and End Sequence, for instance B2 and B3 (see attached example).
In the second worksheet I have a column named: Sequence Ref with over 9000 sequence reference numbers, for example A1 (see attached example).

There are over 9000 records in each workbook (example contains 20 records).

I want Excel to lookup each value in the Sequence Ref column and match it to the relevant Start Sequence and End Sequence columns to see if it falls within the sequence range. Once matched it must either indicate the row number the sequence falls in (or any other method to indicate to the user that the match was found in the relevant start and end constraint).

If it is not found it can return "not found" or leave the answer blank.

I can use the IF, index, match etc but when dragging down the column to return the result for all sequence refs, it does not work.

Hoping someone can guide me or tell me it is not possible?

Many thanks.

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Is this it?

In B2 copied down:

=XLOOKUP(A2,Sheet1!$B$2:$B$20,Sheet1!$A$2:$A$20,"Not Found",-1)

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Provide a workbook that shows the problem.

Please share the solution - don't just leave the thread hanging!