This formula uses more levels of nesting than you can use in the current file format.

[Mike Habinshuti]

I am trying to use my formula:

IF(AND(N7="common basket fund for HIV",U7="WORLD DIABETES FOUNDATION"),"Non costed",IF(AND(N7="common basket fund for HIV",U7="PAYS BAS - NETHERLAND"),"Non costed",IF(AND(N7="common basket fund for HIV",U7=" PROJECT: GLOBAL ALLIANCE FOR VACCINES AND IMMUNISATIONS (FRW)"),"Non costed",IF(AND(N7="common basket fund for HIV",U7="Global fund"),"Non costed",IF(AND(N7="common basket fund for HIV",U7="UNIVERSITY OF THE WITWATERSRAND"),"WOTRO",IF(AND(N7="common basket fund for HIV",U7="UNITED Kingdom"),"BLOOMBERG",IF(AND(N7="common basket fund for HIV",U7="UNATED STATES AGENCY FOR INTERNATIONAL DEVELOPMENT - IDA"),"WDF (NDPCPR)",IF(AND(N7="common basket fund for HIV",U7="United states of america"),"ROROS",IF(AND(N7="common basket fund for HIV",U7="Danemark"),"WDF",IF(AND(N7="common basket fund for HIV",U7="INTERNATIONAL DEVELOPMENT ASSOCIATION"),"PTBI",IF(AND(N7="common basket fund for HIV",U7="Bill gate melinda foundation"),"UICC",IF(AND(N7="common basket fund for HIV",U7="JAPAN"),"SOMITOMO",IF(AND(N7="common basket fund for HIV",U7="World Food Programme"),"WFP",IF(AND(N7="common basket fund for HIV",U7="France"),"IARC",IF(AND(N7="Support health care activities",U7="United states of america"),"blomberg",IF(AND(Q7="government of rwanda",S7="own revenues"),"Own revenues",INDEX('Sup Doc'!$BC$2:$BC$29,MATCH(N7,'Sup Doc'!$BB$2:$BB$29,0))))))))))))))))))

<colgroup><col></colgroup><tbody>
</tbody>

and it's not working getting This formula uses more levels of nesting than you can use in the current file format as message please help.

 

[VBA Geek]

Welcome. The above formula approach is not really efficient. Post your raw data along with your desired results to get some help. To post your data you can use HTML Maker:

https://www.mrexcel.com/forum/excel-questions/628649-recommended-add-ins-links.html

 

[etaf]

i was able to use in 2016 with no problem - Removed the last Index function and replaced with a 0 , as i did not have that sheet etc
And it worked fine

Also what version of Excel you are using as excel 2016 will accept that number of nested IFs
2003 will be an issue and I think 2007 maybe also be a problem
but after that - 2010,13,16 versions should be OK
2010 has 64 levels

Although as stated not the best approach

 

[Gerald Higgins]

Hi, welcome to the board.

That formula is a mess isn't it ?

I think I would try a completely different approach, maybe using some kind of lookup table to store all the permutations.

Something like this . . .

......................Col A........Col B.........Col C.....................Result
Row 1............CBFFH.........WDF.........CBFFH/WDF...........Non Costed
Row 2............CBFFH.........PBN..........CBFFH/PBN............Non Costed
Row 3............CBFFH.........PGAFVAI....CBFFH/PGAFVAI.....Non Costed

and so on.
Obviously I've used abbreviations because I can't be bothered typing your text out in full.

Column C can be a simple formula to concatenate the entries in Col A and Col B, such as
=A1&B1

You can then use a lookup table, something like this

=vlookup(N7&U7,C1:D10,2,false)

Where C1:D10 is the lookup table mentioned above.

This approach won't deal with ALL of your conditions, but should handle most of them in a simpler way.

 

[etaf]

where you have a common output like "non costed" you can combine
=IF(AND(N7="common basket fund for HIV",OR(U7="WORLD DIABETES FOUNDATION", U7="PAYS BAS - NETHERLAND", " PROJECT: GLOBAL ALLIANCE FOR VACCINES AND IMMUNISATIONS (FRW)",U7="Global fund" ),"Non costed",

Note you have a space in front of " Project
is that in excel a space ??? remember excel needs to see the exact text OR you use wild cards *

Also then for the rest where you have
N7="common basket fund for HIV"
in common - you could use a Lookup or a MATCH function

then your left with
IF(AND(N7="Support health care activities",U7="United states of america"),"blomberg",
IF(AND(Q7="government of rwanda",S7="own revenues"),"Own revenues","the index function"))))))))))))))))

 

[thisoldman]

This is a cleaned up version of the original formula:

IF(AND(N7="common basket fund for HIV",  U7="WORLD DIABETES FOUNDATION"),
  "Non costed",
  IF(AND(N7="common basket fund for HIV", U7="PAYS BAS - NETHERLAND"),
    "Non costed",
    IF(AND(N7="common basket fund for HIV" ,U7=" PROJECT: GLOBAL ALLIANCE FOR VACCINES AND IMMUNISATIONS (FRW)"),
     "Non costed",
     IF(AND(N7="common basket fund for HIV",U7="Global fund"),
       "Non costed",
       IF(AND(N7="common basket fund for HIV",U7="UNIVERSITY OF THE WITWATERSRAND"),
         "WOTRO",
         IF(AND(N7="common basket fund for HIV",U7="UNITED Kingdom"),
           "BLOOMBERG",
           IF(AND(N7="common basket fund for HIV",U7="UNATED STATES AGENCY FOR INTERNATIONAL DEVELOPMENT - IDA"),
             "WDF (NDPCPR)",
             IF(AND(N7="common basket fund for HIV",U7="United states of america"),
               "ROROS",
               IF(AND(N7="common basket fund for HIV",U7="Danemark"),
                 "WDF",
                 IF(AND(N7="common basket fund for HIV",U7="INTERNATIONAL DEVELOPMENT ASSOCIATION"),
                   "PTBI",
                   IF(AND(N7="common basket fund for HIV",U7="Bill gate melinda foundation"),
                     "UICC",
                     IF(AND(N7="common basket fund for HIV",U7="JAPAN"),
                       "SOMITOMO",
                       IF(AND(N7="common basket fund for HIV",U7="World Food Programme"),
                         "WFP",
                         IF(AND(N7="common basket fund for HIV",U7="France"),
                           "IARC",
                           IF(AND(N7="Support health care activities",U7="United states of america"),
                             "blomberg",
                             IF(AND(Q7="government of rwanda",S7="own revenues"),
                               "Own revenues",
                               INDEX('Sup Doc'!$BC$2:$BC$29,MATCH(N7,'Sup Doc'!$BB$2:$BB$29,0))))))))))))))))))

This might work as an alternative:

=IF(N7="common basket fund for HIV",
    REPT("Non costed", OR(U7={"WORLD DIABETES FOUNDATION", "PAYS BAS - NETHERLAND",
         " PROJECT: GLOBAL ALLIANCE FOR VACCINES AND IMMUNISATIONS (FRW)", "Global fund"}))
  & REPT("WOTRO", U7="UNIVERSITY OF THE WITWATERSRAND")
  & REPT("BLOOMBERG", U7="UNITED Kingdom")
  & REPT("WDF (NDPCPR)", U7="UNATED STATES AGENCY FOR INTERNATIONAL DEVELOPMENT - IDA")
  & REPT("ROROS", U7="United states of america")
  & REPT("WDF", U7="Danemark")
  & REPT("PTBI", U7="INTERNATIONAL DEVELOPMENT ASSOCIATION")
  & REPT("UICC", U7="Bill gate melinda foundation")
  & REPT("SOMITOMO", U7="JAPAN")
  & REPT("WFP", U7="World Food Programme")
  & REPT("IARC", U7="France"),
 IF(AND(N7="Support health care activities", U7="United states of america"),
    "blomberg",
    IF(AND(Q7="government of rwanda",S7="own revenues"),
       "Own revenues",
       INDEX('Sup Doc'!$BC$2:$BC$29,MATCH(N7,'Sup Doc'!$BB$2:$BB$29,0)))))

I did not correct any possible misspellings that were in the original formula. All strings in quotes are just as they appeared in the original.