Complex IF INDEX MATCH ISNUMBER Formula

N

NJS1982

Board Regular
Joined
Sep 24, 2009
Messages
183
Office Version
  1. 365
Platform
  1. Windows
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:

INDEX:Cap/RevINDEX:Revenue: Project Length (Years)INDEX:Latest Submitted Report from AwardeeINDEX:Result
Capital with Participants12 Month Report12M [1/5]
Capital with Participants24 Month Report24M [2/5]
Capital with Participants36 Month Report36M [3/5]
Capital with Participants48 Month Report48M [4/5]
Capital with Participants60 Month Report60M [5/5]: Completed
Capital with ParticipantsProject Start FormPSF
Capital with ParticipantsNo Reports YetNone
Capital with ParticipantsDe-commitmentN/A - DECOM
Revenue16 Month Report6M [1/2]
Revenue17 Month Report7M [Completed]
Revenue18 Month Report8M [Completed]
Revenue19 Month Report9M [Completed]
Revenue110 Month Report10M [Completed]
Revenue111 Month Report11M [Completed]
Revenue112 Month Report12M [2/2]: Completed
Revenue118 Month ReportCompleted
Revenue124 Month ReportCompleted
Revenue130 Month ReportCompleted
Revenue136 Month ReportCompleted
Revenue142 Month ReportCompleted
Revenue148 Month ReportCompleted
Revenue154 Month ReportCompleted
Revenue160 Month ReportCompleted
Revenue1No Reports YetNone
Revenue1Project Start FormPSF
Revenue26 Month Report6M [1/4]
Revenue212 Month Report12M [2/4]
Revenue213 Month Report13M [Completed]
Revenue214 Month Report14M [Completed]
Revenue215 Month Report15M [Completed]
Revenue216 Month Report16M [Completed]
Revenue217 Month Report17M [Completed]
Revenue218 Month Report18M [3/4]
Revenue219 Month Report19M [Completed]
Revenue220 Month Report20M [Completed]
Revenue221 Month Report21M [Completed]
Revenue222 Month Report22M [Completed]
Revenue223 Month Report23M [Completed]
Revenue224 Month Report24M [4/4]: Completed
Revenue230 Month ReportCompleted
Revenue236 Month ReportCompleted
Revenue242 Month ReportCompleted
Revenue248 Month ReportCompleted
Revenue254 Month ReportCompleted
Revenue260 Month ReportCompleted
Revenue2No Reports YetNone
Revenue2Project Start FormPSF
Revenue36 Month Report6M [1/6]
Revenue312 Month Report12M [2/6]
Revenue318 Month Report18M [3/6]
Revenue324 Month Report24M [4/6]
Revenue325 Month Report25M [Completed]
Revenue326 Month Report26M [Completed]
Revenue327 Month Report27M [Completed]
Revenue328 Month Report28M [Completed]
Revenue329 Month Report29M [Completed]
Revenue330 Month Report30M [5/6]
Revenue331 Month Report31M [Completed]
Revenue332 Month Report32M [Completed]
Revenue333 Month Report33M [Completed]
Revenue334 Month Report34M [Completed]
Revenue335 Month Report35M [Completed]
Revenue336 Month Report36M [6/6]: Completed
Revenue342 Month ReportCompleted
Revenue348 Month ReportCompleted
Revenue354 Month ReportCompleted
Revenue360 Month ReportCompleted
Revenue3No Reports YetNone
Revenue3Project Start FormPSF
Revenue46 Month Report6M [1/8]
Revenue412 Month Report12M [2/8]
Revenue418 Month Report18M [3/8]
Revenue424 Month Report24M [4/8]
Revenue430 Month Report30M [5/8]
Revenue436 Month Report36M [6/8]
Revenue437 Month Report37M [Completed]
Revenue438 Month Report38M [Completed]
Revenue439 Month Report39M [Completed]
Revenue440 Month Report40M [Completed]
Revenue441 Month Report41M [Completed]
Revenue442 Month Report42M [7/8]
Revenue443 Month Report43M [Completed]
Revenue444 Month Report44M [Completed]
Revenue445 Month Report45M [Completed]
Revenue446 Month Report46M [Completed]
Revenue447 Month Report47M [Completed]
Revenue448 Month Report48M [8/8]: Completed
Revenue454 Month ReportCompleted
Revenue460 Month ReportCompleted
Revenue4No Reports YetNone
Revenue4Project Start FormPSF
Revenue56 Month Report6M [1/10]
Revenue57 Month Report7M [Completed]
Revenue58 Month Report8M [Completed]
Revenue59 Month Report9M [Completed]
Revenue510 Month Report10M [Completed]
Revenue511 Month Report11M [Completed]
Revenue512 Month Report12M [2/10]
Revenue513 Month Report13M [Completed]
Revenue514 Month Report14M [Completed]
Revenue515 Month Report15M [Completed]
Revenue516 Month Report16M [Completed]
Revenue517 Month Report17M [Completed]
Revenue518 Month Report18M [3/10]
Revenue519 Month Report19M [Completed]
Revenue520 Month Report20M [Completed]
Revenue521 Month Report21M [Completed]
Revenue522 Month Report22M [Completed]
Revenue523 Month Report23M [Completed]
Revenue524 Month Report24M [4/10]
Revenue525 Month Report25M [Completed]
Revenue526 Month Report26M [Completed]
Revenue527 Month Report27M [Completed]
Revenue528 Month Report28M [Completed]
Revenue529 Month Report29M [Completed]
Revenue530 Month Report30M [5/10]
Revenue531 Month Report31M [Completed]
Revenue532 Month Report32M [Completed]
Revenue533 Month Report33M [Completed]
Revenue534 Month Report34M [Completed]
Revenue535 Month Report35M [Completed]
Revenue536 Month Report36M [6/10]
Revenue537 Month Report37M [Completed]
Revenue538 Month Report38M [Completed]
Revenue539 Month Report39M [Completed]
Revenue540 Month Report40M [Completed]
Revenue541 Month Report41M [Completed]
Revenue542 Month Report42M [7/10]
Revenue543 Month Report43M [Completed]
Revenue544 Month Report44M [Completed]
Revenue545 Month Report45M [Completed]
Revenue546 Month Report46M [Completed]
Revenue547 Month Report47M [Completed]
Revenue548 Month Report48M [8/10]
Revenue549 Month Report49M [Completed]
Revenue550 Month Report50M [Completed]
Revenue551 Month Report51M [Completed]
Revenue552 Month Report52M [Completed]
Revenue553 Month Report53M [Completed]
Revenue554 Month Report54M [9/10]
Revenue555 Month Report55M [Completed]
Revenue556 Month Report56M [Completed]
Revenue557 Month Report57M [Completed]
Revenue558 Month Report58M [Completed]
Revenue559 Month Report59M [Completed]
Revenue560 Month Report60M [10/10]:Completed
Revenue5No Reports YetNone
Revenue5Project Start FormPSF

<tbody>
</tbody>
 
Last edited:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
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.)
 
Upvote 0
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]
 
Upvote 0
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)))))))
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

k3yn0t3
  • Solved
XLOOKUP (multiple criteria) not working
Replies
3
Views
316
k3yn0t3
k3yn0t3
C
  • Question
PIVOT Table Subtract Between Rows
Replies
1
Views
112
number kruncher
N
k3yn0t3
  • Question
Software Biz Analysis for Investing: How to break down customer data based on multiple criteria and show on new tabs
Replies
1
Views
277
k3yn0t3
k3yn0t3
Kellie220
  • Solved
Formula for calculating different dates/ VBA
Replies
3
Views
186
awoohaw
awoohaw
C
  • Question
Revenue Allocation Based on the Number of Weeks ,Days Months etc
Replies
2
Views
130
ChetanPuri
C

Forum statistics

Threads
1,215,391
Messages
6,124,673
Members
449,178
Latest member
Emilou

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top