+ Reply to Thread
Results 1 to 5 of 5

Multi-conditional INDEX, MATCH, VLOOKUP... finding Lat & Long in a geographic square grid

  1. #1
    Registered User
    Join Date
    09-01-2013
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    3

    Multi-conditional INDEX, MATCH, VLOOKUP... finding Lat & Long in a geographic square grid

    Hello. Can Excel perform this solution?

    I have a Latitude and Longitude coordinate and need to lookup which half-mile square "grid" it falls into (a Grid # is in Col E). Essentially, if Y is BETWEEN A&B (A>= Y >=B) AND X is BETWEEN C & D (C>= X >=D), then return Col E.

    I've tried to learn INDEX and MATCH in conjunction with VLOOKUP, but have thrown in the towel. Any one know how to do this?

    Y X Grid
    Example 1 25.985 80.395 = 2.1
    Example 2 25.987 80.407 = 1.3


    A B C D E
    25.993140 25.985897 80.398963 80.391720 1.1
    25.985897 25.978654 80.398963 80.391720 2.1
    25.978654 25.971411 80.398963 80.391720 3.1
    25.971411 25.964168 80.398963 80.39172 4.1
    25.993140 25.985897 80.398963 80.391720 1.1
    25.993140 25.985897 80.406206 80.398963 1.2
    25.993140 25.985897 80.413449 80.406206 1.3
    25.993140 25.985897 80.420692 80.413449 1.4

    Thank you!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,118

    Re: Multi-conditional INDEX, MATCH, VLOOKUP... finding Lat & Long in a geographic square g

    Hi and welcome to the forum

    See if the attached will help...
    Attached Files Attached Files
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    09-01-2013
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Multi-conditional INDEX, MATCH, VLOOKUP... finding Lat & Long in a geographic square g

    Thank you FDibbins. That's closer than I got. Can you think of how to switch it around so that the XY is looking into the array instead of the array looking at a specific XY?

    Examples #1 & #2 (and a thousand more) need to look into an array, then return the grid (col E). Consider that I have hundreds of geographic squares (grids) across an area and need thousands of specific XY coordinates to be assigned a grid (col E). I need many XY coordinates to read into the array then return what's in Col E.

    A friend told me only Sequel can do this. I might have to go that route.

    Thank you!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,118

    Re: Multi-conditional INDEX, MATCH, VLOOKUP... finding Lat & Long in a geographic square g

    OK I get it now...tell your friend they were wrong lol...

    =SUMPRODUCT(($A$2:$A$9>=H1)*($B$2:$B$9<=H1)*($C$2:$C$9>=I1)*($D$2:$D$9<=I1),$E$2:$E$9)
    copied down

  5. #5
    Registered User
    Join Date
    09-01-2013
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Multi-conditional INDEX, MATCH, VLOOKUP... finding Lat & Long in a geographic square g

    Wow, FDibbins. I don't know how that formula works, but it does! Thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Conditional formatting based on VLOOKUP/INDEX:MATCH
    By strud in forum Excel General
    Replies: 2
    Last Post: 06-13-2013, 07:02 AM
  2. Using Index and Match to lookup a value in a grid
    By jcranst in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-05-2012, 01:01 PM
  3. Excel 2007 : Index, Match, or multi Vlookup
    By tlafferty in forum Excel General
    Replies: 3
    Last Post: 04-18-2012, 03:40 AM
  4. Price Grid [index/lookup/match]
    By casey63 in forum Excel General
    Replies: 5
    Last Post: 04-01-2012, 05:26 PM
  5. Staffing Grid - Index Match Help
    By Justinmih in forum Excel General
    Replies: 0
    Last Post: 06-21-2010, 07:27 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1