• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find the range match row number

M

Maxy

New Member
Hi Guys,

In need of your expertise to create a better formula for the attached spreadsheet, basically i want to find the row/record number that is the range of each value in Column I. I think seeing the spreadsheet and the sample results will be more clearer since im not really good at explaining.

I have created a formula that give me the result, but that is just a sample of 2000+ records i need to find and it took more than 10 minutes to run, later on i will need to find the range for 200.000+ records and i cant imagine how slow it will be.

Hope someone can help me on this matter. Thanks
 

Attachments

Hi Maxy,
Sorry I didn't understand the if logic.

Can you check the result by limiting your range?
I've used this formula:
=SUMPRODUCT(--($B$2:$B$5000<=I2)*($C$2:$C$5000>=I2),ROW($B$2:$B$5000))-1

and format the cells:
"Row no. "#;"Not Found";

Can you confirm this, or I have misunderstood?

Regards,
 
wow..i suddenly feel so stupid now...didn't even think about limiting the range...Thanks Khalid, appreciate your help :)
 
Here is an alternative solution using three columns. Using a vlookup to find the closest beginning of range, using index and match to find the end of the range associated with the beginning of the range and then using a third column to see if the value is in the range and if so return the row number otherwise return a not found message. See columns L to N.
 

Attachments

You must log in or register to reply here.
Back
Top