Search for Line Number between Ranges

[jharvey87]

Hi Everyone,

I have a spreadsheet that has a Schedule on one tab and a Register on another tab. Within the Schedule, columns B:C list cost code ranges (a starting number and an ending number). Column A list line numbers (1-40 as example). What I was looking to accomplish, was any time a cost code is listed on the "Register" tab, it would tell me the corresponding line number that cost code falls under. The complicated part is that everything is done in Ranges. So the actual cost code listed might not be on Column B or C, but it falls within the range.

For example, if line 5 has a range of 21013200 and 21013210, and the Register list code 21013205, it would still return line 5 as this falls between those two numbers.

I have attached a spreadsheet as an example to show what I'm trying to do. If this isn't possible, please let me know. Thanks everyone!

[JohnTopley]

Try

=SUMPRODUCT((Schedule!$A$8:$A$54)*(Schedule!$B$8:$B$54<=Register!A8)*(Schedule!$C$8:$C$54>=Register!A8))

[Phuocam]

Or ...

=INDEX(Schedule!$A$8:$A$55,MATCH(1,(Schedule!$B$8:$B$55-A8<=0)*(Schedule!$C$8:$C$55-A8>=0),0))

Enter with Ctrl+Shift+Enter.

[jharvey87]

This is returning a #Value for me. Would you be able to test in the Example sheet i have? Sorry for the delay in responding.

[FlameRetired]

Another way.

Try array entering this and filling down.

Please Login or Register  to view this content.