Results 1 to 6 of 6

Thread: Help on Lookup Formula

  1. #1

    Exclamation Help on Lookup Formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Good Morning/Afternoon whever you are!

    In short i want this
    IFERROR(LOOKUP(2,1/A$3:A$13="x"),C$3:C$13),0) to be able to ignore the blank cell!

    But it keeps giving me a result of 0 because it keeps getting the first cell of the list regardless if its blank or not!

    This is my first post so i cannot give you a visual...

    Thanks,


    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Spreadsheet Preview.PNG 
Views:	26 
Size:	41.0 KB 
ID:	3692  

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,512
    Articles
    0
    Excel Version
    365
    not at all sure, but:
    =IFERROR(LOOKUP(1,1/(A$3:A$13="x"),C$3:C$13),"")

  3. #3
    Quote Originally Posted by p45cal View Post
    not at all sure, but:
    =IFERROR(LOOKUP(1,1/(A$3:A$13="x"),C$3:C$13),"")
    That wouldn't work but I figured it out

    it would be IFERROR(LOOKUP(2,1/((A$3:A$13="x")*(C$3:C$13<>"")),C$3:C$13),0)

    and that works perfectly


  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Another possibility:

    =INDEX(C$3:C$13,MATCH(TRUE,INDEX(A$3:A$13="x",0),0))

    I think the problem with LOOKUP is that a result vector is being used, and with multiple values matching the lookup value its unable to return one value from the result vector.
    Last edited by Hercules1946; 2015-07-12 at 09:36 PM.

  5. #5
    Quote Originally Posted by Hercules1946 View Post
    Another possibility:

    =INDEX(C$3:C$13,MATCH(TRUE,INDEX(A$3:A$13="x",0),0))

    I think the problem with LOOKUP is that a result vector is being used, and with multiple values matching the lookup value its unable to return one value from the result vector.
    The reason I am using a vector is because I need to search from bottom to top.

    But like I mentioned above I figured it out and it works like a charm

    Thank you for your suggestion!

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Glad you found a solution!

Posting Permissions

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