LOOKUP alternative not requiring ascending order sort

Thanks in advance for any and all advice/direction. I'm struggling with an alternative to a particular problem and searches here are not turning up results to point me in the right direction.

The workbook contains multiple sheets, but the two pertinent to this question are; hosts and host_lookup. The hosts sheet contains a list of IP addresses representing unique hosts on a network, and the host_lookup sheet contains 3 columns of data; start_ip, end_ip and cidr. The start_ip and end_ip columns are tied to their CIDR equivalent (i.e. 10.10.0.1/24) and represent the usable range of IP addresses for the network.

I currently use the LOOKUP formula =LOOKUP(H2,A2:B582,C2:C582), where H2 is a host address, A2:B582 being the range of start/end IP addresses and C2:C582 representing the resulting CIDR notation for the range. The formula returns the correct data, but only if the start/end ranges are sorted by ascending order. I'm looking for an alternative to this that does not require sort order of any kind. In short, perform a lookup between two values (i.e. low to high) and return a value from another column on the same row.

Re: LOOKUP alternative not requiring ascending order sort

Thanks, gents! I'll take a look and report back with the solution or additional questions if I have them.

Re: LOOKUP alternative not requiring ascending order sort

I read over the INDEX / MATCH examples at Contextures and Example 4 appears to be the closest to my data set; with the exception that I need to match the lookup_value between a high and low value. I'm sure there is a way to modify the formula to perform this type of comparison, but I don't even know where to begin with that.

I attached a sample workbook that shows the current formula (column highlighted in orange) I use to return the data. As long as the cidr and start columns are sorted by ascending order, the results are accurate. I cannot guarantee that the end-user will have these columns sorted to insure accurate results. The other concern I have is that Example 4 uses an array formula. My data set contains over 17000 rows -- will this adversely impact performance?

I have read multiple VLOOKUP options, but none seem to address a lookup between a high and low value. Perhaps I'm searching for the wrong thing, but I haven't been successful in getting a result that points me in the right direction.

Thanks for any direction.