Lookup Data & Return Left Corresponding Cell

  • I have two sheets of with vehicles that are working days of the week.


    Sheet 1(Log On):


    Vehicle Day P/U Rec'd?
    HN57 OFK Monday
    HN57 OFK Wednesday
    BX07 MKL Monday
    LF08 YJO Tuesday
    NG09 MDV Tuesday



    Sheet 2 (Power Up):


    Vehicle Driver
    HX56 MYH Monday
    HX56 MYH Saturday
    HN57 OFK Monday
    HN57 OFK Tuesday


    I want to perform a vlookup that returns "OK" to the "P/U rec'd" colomn in sheet 1 only if the vehicle powers up on the correct day. i.e, in the above example, the only return value should be on HN57 OFK, Monday.


    How can I go about doing this?
    Thanks,
    Gracey

  • Re: Lookup Data & Return Left Corresponding Cell


    Use a User Defined Function:
    http://www.ozgrid.com/VBA/occurrence-lookup.htm


    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Re: Lookup Data & Return Left Corresponding Cell


    And use INDEX/MATCH combinations if you wish to do this with formuals - check out this post.


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


    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Re: Lookup Data & Return Left Corresponding Cell


    Thank you for such a prompt response!


    But I am afraid I'm still not understanding. I wonder if you could be a bit more specific to my question? The thing is because I have many vehicles in monday, tuesday, wednesday etc, I can't just look up the day and return the response. Similarly I can't look up the vehicle because they may be working more than one day of the week. It needs to match both the day and the vehicle before it returns a response.


    If you have already answered my question and I'm just still not grasping it, I do apologise. I need it spelling out a bit more...


    Many thanks,
    Gracey

  • Re: Lookup Data & Return Left Corresponding Cell


    If you use a "Helper" column on the 2nd sheet, you dont need to use any lookups left or right, a simple countif will do the trick. See attached.

    Files

    • Book1.xls

      (20.48 kB, downloaded 7 times, last: )

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________