Paul McCloskey
New Member
Hi,
I have a complicated challenge in trying to identify the position of a lookup value in a list. The challenge is that, instead of values, the lookup table contains lower and upper boundaries of the values. Also, there is not just one list - but two sets of lists. both sets of criteria must be met. Also, the upper and lower bounds can repeat multiple times - together the two lists side by side are unique but within themselves the same boundaries can repeat.
The attached example hopefully explains as difficult to put into words! It only has 30 rows of data (unique boxes as I call them) and I am trying to find in which unique box three different 'locations' can be found. In my actual file I have 30k rows of data to search and need to find 3k locations within those!
Basically, this is GPS style information. I have two columns that provide the upper and lower bounds for my Latitude number and then the same for Longitude. Each of my outlets has a unique 'Lat' and 'Long'. I need to search down the list and find out where two conditions are met - when my Lat figure is between the Latitude upper and lower bounds AND when the Long figure is within the Long Upper and Lower bounds.
I have played around with match formulas and sum product but cannot figure out a way to do this given the complexity of the data; the two sets of criteria etc.
I have given three examples in attached file. All welcome welcome!
Thanks,
Paul
I have a complicated challenge in trying to identify the position of a lookup value in a list. The challenge is that, instead of values, the lookup table contains lower and upper boundaries of the values. Also, there is not just one list - but two sets of lists. both sets of criteria must be met. Also, the upper and lower bounds can repeat multiple times - together the two lists side by side are unique but within themselves the same boundaries can repeat.
The attached example hopefully explains as difficult to put into words! It only has 30 rows of data (unique boxes as I call them) and I am trying to find in which unique box three different 'locations' can be found. In my actual file I have 30k rows of data to search and need to find 3k locations within those!
Basically, this is GPS style information. I have two columns that provide the upper and lower bounds for my Latitude number and then the same for Longitude. Each of my outlets has a unique 'Lat' and 'Long'. I need to search down the list and find out where two conditions are met - when my Lat figure is between the Latitude upper and lower bounds AND when the Long figure is within the Long Upper and Lower bounds.
I have played around with match formulas and sum product but cannot figure out a way to do this given the complexity of the data; the two sets of criteria etc.
I have given three examples in attached file. All welcome welcome!
Thanks,
Paul