Excel 2008 : SOLVED: Find Last Value Matching Criteria in another cell

[ap0c]

Hello people,

This is my first query so please for give me if I am not as clear as I could be whilst explaining my problem, I have spent ages looking for a solution on the internet (probably looking for the wrong thing due to my lack of knowledge!) and hope that someone here can shed a little light on the situation!

I have a column of data like this:

DNPAL901
DNPAL902
DNPAL903
DNPAN901
DNPAN902
DNPAR001
DNPAR002
DNPAR003
DNPAR004
DNPAR005
DNPAR006
DNPAR015
DNPAR017
DNPAR020
DNPAR021
DNPAR022
DNPAR901
DNPAR902
DNPAR903

I need to be able to find the largest number matching my criteria, for example if D1 was "DNPAR" the result would be 903 or even if D1 was "DNPAR0" the result would be "022" and if D1 was "DNPAM" then it would return "000" (as a not found value).

I hope that my explanation is clear enough, thanks for reading!

[JBeaucaire]

Try this:

=LOOKUP(2, 1/(LEFT($A$1:$A$50, LEN(D1))=D1), RIGHT($A$1:$A$50,3))

Adjust the colored range as needed, don't use a whole column.

[JBeaucaire]

In Excel 2003, the only way to test for "no match" is to run the formula once looking for errors, then run it again if no errors are found, unfortunately. So, to get "000" as a result when the search results in nothing (error), the formula would be:

=IF(ISERROR(LOOKUP(2,1/(LEFT($A$1:$A$50,LEN(D1))=D1))), "000", LOOKUP(2, 1/(LEFT($A$1:$A$50, LEN(D1))=D1), RIGHT($A$1:$A$50,3)))


In Excel 2007, it would be a simple:

=IFERROR(LOOKUP(2, 1/(LEFT($A$1:$A$50, LEN(D1))=D1), RIGHT($A$1:$A$50,3)), "000")

[DonkeyOte]

In Excel 2003, the only way to test for "no match" is to run the formula once looking for errors, then run it again if no errors are found, unfortunately.

Given we know the result here is a 3 digit value this is not strictly true:

Please Login or Register  to view this content.

Here we only perform the potentially expensive LOOKUP once and return 0 ("000") for error.

[ap0c]

Thanks JBeaucaire and DonkeyOte!

That was exactly what I was wanting to do, I had confused myself with the MAX function!

Perfect, thanks so much!

You have made a confused noobie very happy!

[JBeaucaire]

DonkeyOte, that is an awesome construct! It didn't even occur to me to convert the results back to numerics. Thanks!

Ap0c, if that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[snb]

or arrayformula ?

=MAX(IF(LEFT(A1:A20,LEN($D$1))=$D$1,1*RIGHT(A1:A20,3)))