# Lookup values based on longitude and latitude

1. ## Lookup values based on longitude and latitude

I'm attempting to set up a multi-conditional lookup (table 2) that can identify the value for which the adjacent lower and upper latitude and longitude (table 1) values fall between the input (C20 - F20) upper and lower latitude and longitude boundaries (table 2), OR, if there are multiple values with longitude and latitude coordinates that fall between the input boundaries to give the average of these values.

The ultimate purpose of these calculations is to produce raster arrays (such as you would find in a geographical information system like ArcGIS) in excel using coordinates and values alone, structured like table 3.

Note: for the purpose of simplicity I've just used simple values rather than longitude/latitude or eastings/northings

2. ## Re: Lookup values based on longitude and latitude

Raster Array sounds pretty awesome, but I have no clue what that is.

Here's what I came up with:

=SUMPRODUCT((\$C\$6:\$C\$15>=C20)*(\$D\$6:\$D\$15<=D20)*(\$E\$6:\$E\$15>=E20)*(\$F\$6:\$F\$15<=F20)*(G6:G15))/SUMPRODUCT((\$C\$6:\$C\$15>=C20)*(\$D\$6:\$D\$15<=D20)*(\$E\$6:\$E\$15>=E20)*(\$F\$6:\$F\$15<=F20))

This is the average of column G, where the long and lats in the table are within the set parameters.

Each criteria between the parentheses is checking against the parameters, and then summing all values in the last array of G where they are met. The second half of the formula simply identifies the number of matching rows without the final array, which is the denonimator of the average. Sum rows of G where parameters meet divided by the number of rows that met the parameters.

3. ## Re: Lookup values based on longitude and latitude

Cheers mate, that worked perfectly, knew it'd be something simple like that but couldn't work it out for the life of me

My apologies for the jargon: http://webhelp.esri.com/arcgisdeskto...raster_data%3F

Have a good one

There are currently 1 users browsing this thread. (0 members and 1 guests)