Can someone help to shorten or simplify this formula

[Savuti]

Hello, I have tried a few variations but am not able to shorten or simplify this code. It has 4 "IF" conditions which basicaly do the same thing but on different rows. I get a "formula too long" message.

Help would be much appreciated - I have tried but cannot crack this.

Thank you for your help

[FONT=Arial]=IF($A$4="","",IF(ISNUMBER(MATCH($A$4,'D:\Report\MAY\[Plan.xls]Total'!$C$2:$IV$2,0)),INDEX('D:\Report\MAY\[Plan.xls]Total'!C4:IV4,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT]
[FONT=Arial]MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$2:$IV$2,0)),<o:p></o:p>[/FONT]
[FONT=Arial]IF(ISNUMBER(MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$22:$IV$22,0)),INDEX('D:\Report\MAY\[Plan.xls] Total'!C24:IV24,MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$22:$IV$22,0)),<o:p></o:p>[/FONT]
[FONT=Arial]IF(ISNUMBER(MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$33:$IV$33,0)),INDEX('D:\Report\ MAY \[Plan.xls] Total'!C35:IV35,MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$33:$IV$33,0)),<o:p></o:p>[/FONT]
[FONT=Arial]IF(ISNUMBER(MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$44:$IV$44,0)),INDEX('D:\Report\ MAY \[Plan.xls] Total'!C46:IV46,MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$44:$IV$44,0)),"")))))<o:p></o:p>[/FONT]

 

[Savuti]

Just wondering if it is even possible to simplify this formula, or should I be looking at something different altogether. The formula itself does works until I put the last "IF" condition in, then it becomes too long.

Thanks

 

[Aladin Akyurek]

Hello, I have tried a few variations but am not able to shorten or simplify this code. It has 4 "IF" conditions which basicaly do the same thing but on different rows. I get a "formula too long" message.

Help would be much appreciated - I have tried but cannot crack this.

Thank you for your help

[FONT=Arial]=IF($A$4="","",IF(ISNUMBER(MATCH($A$4,'D:\Report\MAY\[Plan.xls]Total'!$C$2:$IV$2,0)),INDEX('D:\Report\MAY\[Plan.xls]Total'!C4:IV4,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT]
[FONT=Arial]MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$2:$IV$2,0)),<o:p></o:p>[/FONT]
[FONT=Arial]IF(ISNUMBER(MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$22:$IV$22,0)),INDEX('D:\Report\MAY\[Plan.xls] Total'!C24:IV24,MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$22:$IV$22,0)),<o:p></o:p>[/FONT]
[FONT=Arial]IF(ISNUMBER(MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$33:$IV$33,0)),INDEX('D:\Report\ MAY \[Plan.xls] Total'!C35:IV35,MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$33:$IV$33,0)),<o:p></o:p>[/FONT]
[FONT=Arial]IF(ISNUMBER(MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$44:$IV$44,0)),INDEX('D:\Report\ MAY \[Plan.xls] Total'!C46:IV46,MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$44:$IV$44,0)),"")))))<o:p></o:p>[/FONT]

If the expected outcome is a text value:

=LOOKUP(REPT("z",255),CHOOSE({1,2,3,4,5},"",
    INDEX('D:\Report\ MAY \[Plan.xls] Total'!C46:IV46,
        MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$44:$IV$44,0)),
      INDEX('D:\Report\ MAY \[Plan.xls] Total'!C35:IV35,
        MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$33:$IV$33,0));
      INDEX('D:\Report\MAY\[Plan.xls] Total'!C24:IV24,
        MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$22:$IV$22,0)),
      INDEX('D:\Report\MAY\[Plan.xls]Total'!C4:IV4,
        MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$2:$IV$2,0))))

If a numeric outcome is expected:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3,4,5},0,
   INDEX('D:\Report\ MAY \[Plan.xls] Total'!C46:IV46,
     MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$44:$IV$44,0)),
   INDEX('D:\Report\ MAY \[Plan.xls] Total'!C35:IV35,
     MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$33:$IV$33,0));
   INDEX('D:\Report\MAY\[Plan.xls] Total'!C24:IV24,
     MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$22:$IV$22,0)),
   INDEX('D:\Report\MAY\[Plan.xls]Total'!C4:IV4,
     MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$2:$IV$2,0))))

<o></o>

 

[Savuti]

Thanks for the reply Aladin, there is a possibility that the result could be either numeric or text, is there a way to combine these two formulas you have suggested.

Thank you

 

[Jonmo1]

Will the value in A4 ever exist in any row OTHER THAN 2 22 33 or 44 ?
And will it only appear once (or not at all) in those 4 rows?

 

[Savuti]

jonmo1 "A4" is actually a date, so rows 2, 22, 33 ,44 will contain all the dates of the months for the entire year - Jan to Dec. So the formula needs to scan those rows across all the columns and find and match the date in "A4" then pull the data from the appropriate cell.

Hope this helps

 

[Jonmo1]

That doesn't quite answer the question....

Do dates (or even numbers) also appear in OTHER rows besides 2 22 33 and 44?
Is it possible for the value in A4 to find a match in a row other than those 4 rows?

What I'm saying is it is possible to search the entire range row 2 through 44 for the date..
Instead of 4 searches through just single rows at a time..

But if it's possible for that date to also exist in say row 13, then this approach won't work.

 

[Savuti]

The date should only appear in those 4 rows, but there is a possiblility that the date or numbers could appear in other rows as well, so I think the formula needs to focus specifically on those 4 rows.

Thanks

 

[Aladin Akyurek]

Thanks for the reply Aladin, there is a possibility that the result could be either numeric or text, is there a way to combine these two formulas you have suggested.

Thank you

Define BigStr as referring to:

=REPT("z",255)

=LOOKUP(BigStr,CHOOSE({1,2,3,4,5},"",
   INDEX('D:\Report\ MAY \[Plan.xls] Total'!C46:IV46&"",
     MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$44:$IV$44,0)),
   INDEX('D:\Report\ MAY \[Plan.xls] Total'!C35:IV35&"",
     MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$33:$IV$33,0));
   INDEX('D:\Report\MAY\[Plan.xls] Total'!C24:IV24&"",
     MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$22:$IV$22,0)),
   INDEX('D:\Report\MAY\[Plan.xls]Total'!C4:IV4&"",
     MATCH($A$4,'D:\Report\MAY\[Plan.xls] Total'!$C$2:$IV$2,0))))

Let X2 house the foregoing formula.

Usage of X2 as number:

=SUM(IF(ISNUMBER(X2+0),X2,0),Z2)

Usage of X2 as text should be pretty straightforward.

 

[Savuti]

Aladin, I will try this now.

Thanks