the specified formula cannot be entered because it uses more levels of nesting than are allowed

[sammy7]

Hi

Can you please help me shorten this formula so the excel can accept it without giving an error.

=IFERROR(IF(AG2="","",IF(AG2="Primary",D2,IF(LEFT(E2,LEN(E2))=LEFT(AI2,LEN(E2)),D2,IF(COUNTIF([PERSONAL.XLSB]Sheet1!$AA1:$AA200,LEFT(LEFT(AI2, FIND(".",AI2, FIND(".",AI2)+1)),LEN(LEFT(AI2, FIND(".",AI2, FIND(".",AI2)+1)))-1))>0,VLOOKUP(LEFT(LEFT(AI2, FIND(".",AI2, FIND(".",AI2)+1)),LEN(LEFT(AI2, FIND(".",AI2, FIND(".",AI2)+1)))-1),[PERSONAL.XLSB]Sheet1!$AA1:$AB200,2,0),INDIRECT("D"&MATCH(AF2&"Primary",AF:AF&AG:AG,0))))),"Not Found")

 

[RCBricker]

this fits

=IFERROR(IF(AG2="","",IF(OR(AG2="Primary",LEFT(E2,LEN(E2))=LEFT(AI2,LEN(E2))),D2,IF(COUNTIF([PERSONAL.XLSB]Sheet1!$AA1:$AA200,LEFT(LEFT(AI2, FIND(".",AI2, FIND(".",AI2)+1)),LEN(LEFT(AI2, FIND(".",AI2, FIND(".",AI2)+1)))-1))>0,VLOOKUP(LEFT(LEFT(AI2, FIND(".",AI2, FIND(".",AI2)+1)),LEN(LEFT(AI2, FIND(".",AI2, FIND(".",AI2)+1)))-1),[PERSONAL.XLSB]Sheet1!$AA1:$AB200,2,0),INDIRECT("D"&MATCH(AF2&"Primary",AF:AF&AG:AG,0))))),"Not Found")

 

[sammy7]

Thank you so much for your response. It fits no doubt, but there was some inconsistency with the data which gave trouble afterwards. So ended up fixing that and further shortening the formula to:

=IFERROR(IF(AG2="","",IF(AG2="Primary",D2,IFERROR(INDIRECT("D"&MATCH(AF2&"Primary",AF:AF&AG:AG,0)),VLOOKUP(LEFT(LEFT(AI2, FIND(".",AI2, FIND(".",AI2)+1)),LEN(LEFT(AI2, FIND(".",AI2, FIND(".",AI2)+1)))-1),[PERSONAL.XLSB]Sheet1!$AA1:$AB200,2,0)))),"Not  Found")