Lookup in a cell range

[Harvey12]

Hey guys!

Need some help! I've got a list of many ranges of numbers, these are all ranges for serial numbers for example 1-10 or 11-20. These are separated over two columns. Column D is the start of the serial number range, Column E is the end of the range. I want to be able to search a number and that show me which serial number range that comes into.

So here is what I've been working on:
=IF(SUMPRODUCT(--(D3:D6275<=J9)*(E3:E6275>=J9))=1,"Row no. "&SUMPRODUCT(--(D3:D6275<=J9)*(E3:E6275>=J9),ROW(D3:D6275))-2,"Not Found")

And I've hit a roadblock and need some help! First of the formula isn't working properly, seems to work for anything in my second row but none others, secondly some of the ranges are on multiple rows because more than one product was assigned to that range for example:

Serial range 1-10 can have 2 products assigned to that range, but this would be on separate rows on the sheet.

J9 is currently the cell of which the number I am searching is typed.

PLEASE HELP!!

Many thanks
Harvey

 

[Aladin Akyurek]

Try to post a scaled-down example sample along with the results that must obtain.

 

[Harvey12]

Hi there,

Thank you for getting back to me! The file is confidential but I can't access websites such as dropbox anyway!

Would a copy and paste suffice? I have also slightly changed my formula!

 

[Aladin Akyurek]

Hi there,

Thank you for getting back to me! The file is confidential but I can't access websites such as dropbox anyway!

Would a copy and paste suffice? I have also slightly changed my formula!

You can create a fake sample. No need to post a formula that doesn't work. Better describe your problem in words and provide a small representative sample, directly on this board (not an image, also not a link to an outside file.). For posting, try one of: http://www.mrexcel.com/forum/about-board/508133-attachments.html or https://app.box.com/s/soezox25h3w0q5s4rcyl
<strike></strike>