Complex IF INDEX MATCH ISNUMBER Formula

[NJS1982]

Hi, after posting a few weeks back now, I have come back to a conundrum of a formula.

I basically have a list of projects, all of whom report back to me on a 6-monthly cycle, from anything between 1 and 5 years (so 6 month report,12 month report,18,24,30,36,42,48,54 and 60 etc.).

However there are a few that are exceptions to this general rule; one project for example runs for 2 years and three months, their final report will come to me labelled as a '27 Month Report' (having received already a 6 month, 12 month, 18 month and a 24 month report).

I basically have a large formula that says something along the lines of,
- If a cell contains text that says '6 Month Report' then the result will be '6M [1/2]'.
- If a cell contains text that says '12 Month Report' then the result will be '12M [2/2]'.

What I am wanting to do is if the cell contains text that says '27 Month Report' (basically anything that is not the normal expected '6' or '12' month), then it will say something like '27M [Completed]'.

The formula I am using is a lot more complex than this, has other dependencies from other cells and it is rather large - I tried doing some additional 'IFs' within the formula, but was told by Excel that I can't have more than 64 nested IF's so ideally need an alternative.

Advice on my previous post was to use a formula like this:

=IF(A5="","",IF(L5<>"",INDEX($AI$5:$AI$152,MATCH(1,INDEX(($AF$5:$AF$152=D5)*(ISNUMBER(SEARCH($AH$5:$AH$152,AD5)))*($AG$5:$AG$152=L5),0),0)),INDEX($AI$5:$AI$152,MATCH(1,INDEX(($AF$5:$AF$152=D5)*(ISNUMBER(SEARCH($AH$5:$AH$152,AD5))),0),0))))

A5 is the unique record number (URN), L5 is the number of rounded up years a project is scheduled to run (between 1 and 5), AD5 is the name of the latest report (i.e. 23 Month Report).
The Index table is below, running from columns AF4 to AI5:

[TABLE="class: grid, width: 650, align: center"]
<tbody>[TR]
[TD="align: center"]INDEX:Cap/Rev[/TD]
[TD="align: center"]INDEX:Revenue: Project Length (Years)[/TD]
[TD="align: center"]INDEX:Latest Submitted Report from Awardee[/TD]
[TD="align: center"]INDEX:Result[/TD]
[/TR]
[TR]
[TD="align: center"]Capital with Participants[/TD]
[TD="align: center"][/TD]
[TD="align: center"]12 Month Report[/TD]
[TD="align: center"]12M [1/5][/TD]
[/TR]
[TR]
[TD="align: center"]Capital with Participants[/TD]
[TD="align: center"][/TD]
[TD="align: center"]24 Month Report[/TD]
[TD="align: center"]24M [2/5][/TD]
[/TR]
[TR]
[TD="align: center"]Capital with Participants[/TD]
[TD="align: center"][/TD]
[TD="align: center"]36 Month Report[/TD]
[TD="align: center"]36M [3/5][/TD]
[/TR]
[TR]
[TD="align: center"]Capital with Participants[/TD]
[TD="align: center"][/TD]
[TD="align: center"]48 Month Report[/TD]
[TD="align: center"]48M [4/5][/TD]
[/TR]
[TR]
[TD="align: center"]Capital with Participants[/TD]
[TD="align: center"][/TD]
[TD="align: center"]60 Month Report[/TD]
[TD="align: center"]60M [5/5]: Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Capital with Participants[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Project Start Form[/TD]
[TD="align: center"]PSF[/TD]
[/TR]
[TR]
[TD="align: center"]Capital with Participants[/TD]
[TD="align: center"][/TD]
[TD="align: center"]No Reports Yet[/TD]
[TD="align: center"]None[/TD]
[/TR]
[TR]
[TD="align: center"]Capital with Participants[/TD]
[TD="align: center"][/TD]
[TD="align: center"]De-commitment[/TD]
[TD="align: center"]N/A - DECOM[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]6 Month Report[/TD]
[TD="align: center"]6M [1/2][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7 Month Report[/TD]
[TD="align: center"]7M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]8 Month Report[/TD]
[TD="align: center"]8M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]9 Month Report[/TD]
[TD="align: center"]9M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]10 Month Report[/TD]
[TD="align: center"]10M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]11 Month Report[/TD]
[TD="align: center"]11M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]12 Month Report[/TD]
[TD="align: center"]12M [2/2]: Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]18 Month Report[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]24 Month Report[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]30 Month Report[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]36 Month Report[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]42 Month Report[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]48 Month Report[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]54 Month Report[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]60 Month Report[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]No Reports Yet[/TD]
[TD="align: center"]None[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Project Start Form[/TD]
[TD="align: center"]PSF[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6 Month Report[/TD]
[TD="align: center"]6M [1/4][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]12 Month Report[/TD]
[TD="align: center"]12M [2/4][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]13 Month Report[/TD]
[TD="align: center"]13M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]14 Month Report[/TD]
[TD="align: center"]14M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]15 Month Report[/TD]
[TD="align: center"]15M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]16 Month Report[/TD]
[TD="align: center"]16M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]17 Month Report[/TD]
[TD="align: center"]17M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]18 Month Report[/TD]
[TD="align: center"]18M [3/4][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]19 Month Report[/TD]
[TD="align: center"]19M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]20 Month Report[/TD]
[TD="align: center"]20M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]21 Month Report[/TD]
[TD="align: center"]21M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]22 Month Report[/TD]
[TD="align: center"]22M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]23 Month Report[/TD]
[TD="align: center"]23M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]24 Month Report[/TD]
[TD="align: center"]24M [4/4]: Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]30 Month Report[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]36 Month Report[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]42 Month Report[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]48 Month Report[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]54 Month Report[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]60 Month Report[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]No Reports Yet[/TD]
[TD="align: center"]None[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Project Start Form[/TD]
[TD="align: center"]PSF[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]6 Month Report[/TD]
[TD="align: center"]6M [1/6][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]12 Month Report[/TD]
[TD="align: center"]12M [2/6][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]18 Month Report[/TD]
[TD="align: center"]18M [3/6][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]24 Month Report[/TD]
[TD="align: center"]24M [4/6][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]25 Month Report[/TD]
[TD="align: center"]25M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]26 Month Report[/TD]
[TD="align: center"]26M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]27 Month Report[/TD]
[TD="align: center"]27M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]28 Month Report[/TD]
[TD="align: center"]28M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]29 Month Report[/TD]
[TD="align: center"]29M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]30 Month Report[/TD]
[TD="align: center"]30M [5/6][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]31 Month Report[/TD]
[TD="align: center"]31M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]32 Month Report[/TD]
[TD="align: center"]32M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]33 Month Report[/TD]
[TD="align: center"]33M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]34 Month Report[/TD]
[TD="align: center"]34M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]35 Month Report[/TD]
[TD="align: center"]35M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]36 Month Report[/TD]
[TD="align: center"]36M [6/6]: Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]42 Month Report[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]48 Month Report[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]54 Month Report[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]60 Month Report[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]No Reports Yet[/TD]
[TD="align: center"]None[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Project Start Form[/TD]
[TD="align: center"]PSF[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6 Month Report[/TD]
[TD="align: center"]6M [1/8][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]12 Month Report[/TD]
[TD="align: center"]12M [2/8][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]18 Month Report[/TD]
[TD="align: center"]18M [3/8][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]24 Month Report[/TD]
[TD="align: center"]24M [4/8][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]30 Month Report[/TD]
[TD="align: center"]30M [5/8][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]36 Month Report[/TD]
[TD="align: center"]36M [6/8][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]37 Month Report[/TD]
[TD="align: center"]37M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]38 Month Report[/TD]
[TD="align: center"]38M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]39 Month Report[/TD]
[TD="align: center"]39M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]40 Month Report[/TD]
[TD="align: center"]40M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]41 Month Report[/TD]
[TD="align: center"]41M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]42 Month Report[/TD]
[TD="align: center"]42M [7/8][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]43 Month Report[/TD]
[TD="align: center"]43M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]44 Month Report[/TD]
[TD="align: center"]44M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]45 Month Report[/TD]
[TD="align: center"]45M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]46 Month Report[/TD]
[TD="align: center"]46M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]47 Month Report[/TD]
[TD="align: center"]47M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]48 Month Report[/TD]
[TD="align: center"]48M [8/8]: Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]54 Month Report[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]60 Month Report[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]No Reports Yet[/TD]
[TD="align: center"]None[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Project Start Form[/TD]
[TD="align: center"]PSF[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6 Month Report[/TD]
[TD="align: center"]6M [1/10][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]7 Month Report[/TD]
[TD="align: center"]7M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]8 Month Report[/TD]
[TD="align: center"]8M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]9 Month Report[/TD]
[TD="align: center"]9M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]10 Month Report[/TD]
[TD="align: center"]10M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]11 Month Report[/TD]
[TD="align: center"]11M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]12 Month Report[/TD]
[TD="align: center"]12M [2/10][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]13 Month Report[/TD]
[TD="align: center"]13M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]14 Month Report[/TD]
[TD="align: center"]14M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]15 Month Report[/TD]
[TD="align: center"]15M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]16 Month Report[/TD]
[TD="align: center"]16M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]17 Month Report[/TD]
[TD="align: center"]17M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]18 Month Report[/TD]
[TD="align: center"]18M [3/10][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]19 Month Report[/TD]
[TD="align: center"]19M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]20 Month Report[/TD]
[TD="align: center"]20M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]21 Month Report[/TD]
[TD="align: center"]21M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]22 Month Report[/TD]
[TD="align: center"]22M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]23 Month Report[/TD]
[TD="align: center"]23M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]24 Month Report[/TD]
[TD="align: center"]24M [4/10][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]25 Month Report[/TD]
[TD="align: center"]25M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]26 Month Report[/TD]
[TD="align: center"]26M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]27 Month Report[/TD]
[TD="align: center"]27M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]28 Month Report[/TD]
[TD="align: center"]28M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]29 Month Report[/TD]
[TD="align: center"]29M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]30 Month Report[/TD]
[TD="align: center"]30M [5/10][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]31 Month Report[/TD]
[TD="align: center"]31M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]32 Month Report[/TD]
[TD="align: center"]32M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]33 Month Report[/TD]
[TD="align: center"]33M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]34 Month Report[/TD]
[TD="align: center"]34M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]35 Month Report[/TD]
[TD="align: center"]35M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]36 Month Report[/TD]
[TD="align: center"]36M [6/10][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]37 Month Report[/TD]
[TD="align: center"]37M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]38 Month Report[/TD]
[TD="align: center"]38M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]39 Month Report[/TD]
[TD="align: center"]39M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]40 Month Report[/TD]
[TD="align: center"]40M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]41 Month Report[/TD]
[TD="align: center"]41M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]42 Month Report[/TD]
[TD="align: center"]42M [7/10][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]43 Month Report[/TD]
[TD="align: center"]43M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]44 Month Report[/TD]
[TD="align: center"]44M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]45 Month Report[/TD]
[TD="align: center"]45M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]46 Month Report[/TD]
[TD="align: center"]46M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]47 Month Report[/TD]
[TD="align: center"]47M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]48 Month Report[/TD]
[TD="align: center"]48M [8/10][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]49 Month Report[/TD]
[TD="align: center"]49M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]50 Month Report[/TD]
[TD="align: center"]50M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]51 Month Report[/TD]
[TD="align: center"]51M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]52 Month Report[/TD]
[TD="align: center"]52M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]53 Month Report[/TD]
[TD="align: center"]53M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]54 Month Report[/TD]
[TD="align: center"]54M [9/10][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]55 Month Report[/TD]
[TD="align: center"]55M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]56 Month Report[/TD]
[TD="align: center"]56M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]57 Month Report[/TD]
[TD="align: center"]57M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]58 Month Report[/TD]
[TD="align: center"]58M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]59 Month Report[/TD]
[TD="align: center"]59M [Completed][/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]60 Month Report[/TD]
[TD="align: center"]60M [10/10]:Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]No Reports Yet[/TD]
[TD="align: center"]None[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Project Start Form[/TD]
[TD="align: center"]PSF[/TD]
[/TR]
</tbody>[/TABLE]

 

[Chrisdontm]

Re: Complex IF INDEX MATCH ISNUMBER Formula - help!

I am a little confused on what your reaching for, and where it is to go.
It looks like the columns you have are to be columns; AF, AG, AH, & AI
Then it looks like you are wanting to reference each of the first three columns to get the answer for the fourth column.
But the fourth column is referenced in the formula...???
I'm sorry, just not sure...


(One thing about what you said about the many IF formulas.
You could use helper cells to put some of the answers then reference those cells.)

 

[NJS1982]

Re: Complex IF INDEX MATCH ISNUMBER Formula - help!

Hi, I think I may have just over complicated it.
Basically, I need the entry in a cell to look at:

- A report name (e.g. 30 Month Report)
- The number of years for the record (e.g. 3 years)
- What type it is (e.g. Revenue)

...then provide the response, which for the above would be 30M [5/6]

If the above bullet examples were 12 Month Report, 4 years, Revenue, the return would be 12M [2/8]

And if it were a 24 Month Report, 3 years, Revenue, it would be 24M [4/6]

 

[NJS1982]

Re: Complex IF INDEX MATCH ISNUMBER Formula - help!

I may have cracked it... kind of....

=IF(A5="","",IF(L5=1,INDEX($AG$13:$AJ$29,MATCH(AD5,$AI$13:$AI$29,0),4),IF(L5=2,INDEX($AG$30:$AJ$51,MATCH(AD5,$AI$30:$AI$51,0),4),IF(L5=3,INDEX($AG$52:$AJ$73,MATCH(AD5,$AI$52:$AI$73,0),4),IF(L5=4,INDEX($AG$74:$AJ$95,MATCH(AD5,$AI$74:$AI$95,0),4),IF(L5=5,INDEX($AG$96:$AJ$152,MATCH(AD5,$AI$96:$AI$152,0),4)))))))

 

[Chrisdontm]

Re: Complex IF INDEX MATCH ISNUMBER Formula - help!

From looking at your second post, it appears that you are wanting the answer to be in the fourth column, yet in your next post you use that fourth column in your formula...

And now your ranges have all moved over one column as though you inserted a column, which is fine, I just need to see consistency...

So where are you wanting to put this formula you are working on?
Will it be in one cell only, or will it get copied all the way down the column?

 

[Mike Szczesny]

Re: Complex IF INDEX MATCH ISNUMBER Formula - help!

Perhaps if you provide a small data sample and show the results you need. This would make it easier for all us to know exactly what you need.