+ Reply to Thread
Results 1 to 8 of 8

Lookup Query

  1. #1
    Registered User
    Join Date
    06-02-2011
    Location
    London,England
    MS-Off Ver
    Excel 2007
    Posts
    13

    Cool Lookup Query

    I was wondering if someone can help me. I am looking for a formula that can pick up the last with content of X01, X02 etc.. within a Row. As i need the formula to pick up only cells that have "X"

    I am currently using =IF(A17="","",(LOOKUP("xx",L17:AE17))) to pick up the any content but i need another one that pick up specfic cell that Contain X01, X02 etc,

    See attachment as reference

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Lookup Query

    clear as mud,where is the formula? what exactly do you mean,what do you expect the result to be?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    06-02-2011
    Location
    London,England
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Lookup Query

    Basically what i want to achieve is Cell E17 to find the the latest version of only X01, X02 X03 etc... (from range L17:AE17), in a nutshell i only want to E17 to pick up X## etc..

    Any help would be greatly appreciated

    Rich
    Last edited by Cutter; 08-23-2012 at 12:29 PM. Reason: Removed double post

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Lookup Query

    Try
    =lookup(2,1/(left(l17:ae17)="x"),l17:ae17)

  5. #5
    Registered User
    Join Date
    06-02-2011
    Location
    London,England
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Lookup Query

    Thanks mate that is spot on.

    Can I ask another query. If I want to do the same thing but pick up anything with "P" or "C" what would the formula be

    Thanks in advance

    Rich

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Lookup Query

    =lookup(2,1/(left(l17:ae17)="p"),l17:ae17)


    =lookup(2,1/(left(l17:ae17)="c"),l17:ae17)

  7. #7
    Registered User
    Join Date
    06-02-2011
    Location
    London,England
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Lookup Query

    Is there a way that it could pick up either "C" or "P" whichever is the last one in the row's range?

    Eg Cell G21 i want to pick up C01 as its the furthest along in that screen shot

    Thanks again

    Excel.jpg

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Lookup Query

    =LOOKUP(2,1/((LEFT(L17:AE17)="p")+(LEFT(L17:AE17)="c")),L17:AE17)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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