+ Reply to Thread
Results 1 to 8 of 8

find last value in a column if it meets a certain criteria

  1. #1
    Forum Contributor
    Join Date
    07-08-2012
    Location
    beirut
    MS-Off Ver
    Excel 2010
    Posts
    102

    find last value in a column if it meets a certain criteria

    Hey there
    I need help please to find last non blank cell in a column, and check if its corresponding cell in another column contains the word "L1", and return its corresponding date from another column.
    Here is an attached example for better understanding.
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,156

    Re: find last value in a column if it meets a certain criteria

    hi chiidzzz, try copying this into the formula bar:
    =INDEX(A1:A9,MAX(ISNUMBER(FIND("L1",B1:B9))*ROW(B1:B9)))

    after that, press CTRL + SHIFT + ENTER

    you may change the range as much as you want. I just don't like to lookup the whole column. it'll slow down Excel

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Contributor
    Join Date
    07-08-2012
    Location
    beirut
    MS-Off Ver
    Excel 2010
    Posts
    102

    Re: find last value in a column if it meets a certain criteria

    Thanks alot
    Could you please explain what is the job of the ISNUMBER function in this formula?

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: find last value in a column if it meets a certain criteria

    Maybe safer with ...
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Confirm with Ctrl+Shift+Enter

    This will allow for blanks in Column C, and SEARCH() will handle "L1" and "l1" it isn't case-sensitive, FIND() is.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  5. #5
    Forum Contributor
    Join Date
    07-08-2012
    Location
    beirut
    MS-Off Ver
    Excel 2010
    Posts
    102

    Re: find last value in a column if it meets a certain criteria

    Thanks for your reply, but i still have 2 questions.
    It only works if the range starts from 1, like A1:A:10, if i change it to A3:A10 it doesn't work.
    And is it possible to add more criterias?

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: find last value in a column if it meets a certain criteria

    All the ranges must be the same size
    Try
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Earlier you asked about ISNUMBER()
    SEARCH() & FIND() both return a number if the search string is found in the data string.
    If the string isn't found a #VALUE! error is returned
    So
    =ISNUMBER(10) returns TRUE
    =ISNUMBER(#VALUE!) returns FALSE

    This can then be coerced to
    =ISNUMBER(10)*1 returns 1
    =ISNUMBER(#VALUE!)*1 returns 0


    What and where are the extra conditions you need to apply?
    There are many different ways to handle this, it all depends on what the conditions are.

  7. #7
    Forum Contributor
    Join Date
    07-08-2012
    Location
    beirut
    MS-Off Ver
    Excel 2010
    Posts
    102

    Re: find last value in a column if it meets a certain criteria

    Ok i figured it out:
    If i want the range to be A4:A10, the formula will be:
    =INDEX($A$4:$A$10,SUM(MAX(IF(ISNUMBER($B$4:$B$10)*ISNUMBER(SEARCH("L1*",$B$4:$B$10)),ROW($A$4:$A$10)))-3))
    As for adding criteria, this one works:
    =INDEX($A$4:$A$10,SUM(MAX(IF(ISNUMBER($B$4:$B$10)*ISNUMBER(SEARCH("L1*",$B$4:$B$10)*ISNUMBER(SEARCH("CRITERIA",$D$4:$D$10))),ROW($A$4:$A$10)))-3))

    Thank you all !

  8. #8
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: find last value in a column if it meets a certain criteria

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ 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