+ Reply to Thread
Results 1 to 6 of 6

Looking up values within a range that has a unique id

  1. #1
    Registered User
    Join Date
    05-13-2012
    Location
    new zealand
    MS-Off Ver
    Excel 2010
    Posts
    3

    Looking up values within a range that has a unique id

    Hard to explain but ill try my best, thanks in advance

    In a sheet I have roads, carriageways (part roads, referred to as cwayX) with various start and ends - e.g road1 cway1 1m-100m, road1 cway2 100m-200m, road2 cway1 1m-100m, road 2 cway2 100m-200m, road3 cway1 1m-100m, road3 cway2 100m-200m etc.
    In another sheet I have roads with locations - e.g road1 38m, road3 104m etc.

    How do I get the carriageway ID into the 2nd sheet?
    edit: I should add, attached example sheet. The real one has thousands of rows

    Cheers
    Brad
    Attached Files Attached Files
    Last edited by plox; 05-13-2012 at 08:57 PM.

  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,239

    Re: Looking up values within a range that has a unique id

    what are you basing your 'search" on? in your example, sheet2 only has 2 references to gun rd, while sheet1 has 3?
    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
    05-13-2012
    Location
    new zealand
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Looking up values within a range that has a unique id

    the location (sheet2) of an item being within the start and end (sheet1)

    move start in front of cway_id, sorry didn't see it when I made the sheet
    I was thinking something along the lines of:
    =VLOOKUP(B2,Sheet1!B2:C9,2,TRUE)
    But it gives the incorrect result unless the range is changed to B2:C4 (see below)

    when the road name on Sheet2 is gun rd, the vlookup range needs to change to only rows starting with gun rd,
    so for gun rd, in this case B2:C4. Turtle rd would be B5:C6 etc.

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,454

    Re: Looking up values within a range that has a unique id

    PL see the attached file.
    Array Formula is available in C column of Sheet2.You can drag down.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-13-2012
    Location
    new zealand
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Looking up values within a range that has a unique id

    wow, yea that is way over my head.
    Anyway cheers!

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,454

    Re: Looking up values within a range that has a unique id

    Thank You.
    Pl Mark the thread solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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