multiple criteria search and extraction from string!

1. The first worksheet contains a database.

2. The column headers can be seen in attached file.

3. Budget-Spent-Forecast attribute can be
1. Committed
2. Forecast

4.CW attribute is year followed by week number
example:
2014-35
2015-06
2015-08

5. Year attribute lists the year


6.Here is my problem. I want to figure out a way (function or a macro), that will run through the database and find the latest entry for the attribute ' committed'
ie.
committed - latest year

Then it proceeds to extract the week number. Then it exports the week number as 'CWweeknumber' to a cell in a different worksheet in the same file.

Any help would be grateful! Thank you!CWexcelmacro.xlsx

The output in this case is 03, not 04 since 2014-04 entry is a forecast. CWexcelmacro (2).xlsx

Originally Posted by nflsales

Can you add your expected result manually so that it is easy to understand

hi oasafox .

Against Forum Rules Duplicate Thread
http://www.excelforum.com/excel-form...g-strings.html

Punnma

=RIGHT(INDEX(Basis!$U$4:$U$90000;MAX(IF(LEFT(Basis!$U$4:$U$90000;4)*1=MAX(LEFT(Basis!$U$4:$U$90000)*1);IF(Basis!$I$4:$I$90000="committed";ROW(Basis!$U$4:$U$90000)-ROW(Basis!$U$4)+1))));2)

I edited the formula to this to fit it to my database, but now I get #value error

Originally Posted by sktneer

Post deleted due to violation of Forum Rule: 5 by the OP.

nflsales.. .solution WORKS.

Could you also please explain what these criteria for Looupfunction mean? so that I can use it again.

2;
1/((Basis!$U:$U<>"")*(Basis!I:I="Committed"))

original thread: