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

**chiidzzz** · 07-15-2012, 06:01 AM

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.

**benishiryo** · 07-15-2012, 07:00 AM

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

**chiidzzz** · 07-15-2012, 07:19 AM

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

**Marcol** · 07-15-2012, 07:22 AM

Maybe safer with ...

Formula:

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.

**chiidzzz** · 07-15-2012, 07:58 AM

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?

**Marcol** · 07-15-2012, 08:21 AM

All the ranges must be the same size
Try

Formula:

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.

**chiidzzz** · 07-15-2012, 02:01 PM

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 !

**Cutter** · 07-15-2012, 02:19 PM

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