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.