LOOKUP alternative not requiring ascending order sort

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • 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


    Or, consider using an INDEX-MATCH formula.


    For the record, sorting the data in ascending order (look up column) will improve the search


    A few links on the topic:


    http://www.ozgrid.com/Excel/left-lookup.htm


    http://www.contextures.com/xlfunctions03.html


    http://www.databison.com/index…use-excel-match-function/

  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!