have i reached the maximum number of nested functions?

hobgoblinvasya

Board Regular
Joined
Jun 29, 2005
Messages
215
hi all

as the title says i have the following problem:

the formula:
Code:
=IF(B40=0,"",IF(ISERROR(INDEX(F1:F30,MATCH(INDEX(N1:Y10000,MATCH(TRIM(B40),Z1:Z10000,0),3),G1:G30,0))),INDEX(N1:Y10000,MATCH(TRIM(B40),Z1:Z10000,0),3),INDEX(F1:F30,MATCH(INDEX(N1:Y10000,MATCH(TRIM(B40),Z1:Z10000,0),3),G1:G30,0))&SI(HJ40="Secteur C-ville MTL"," CV","")))

Now if i add the following error check to it at the beginning of the formula (i simply put 0 both times to just test the formula)....
Code:
=if(iserror(IF(B40=0,"",IF(ISERROR(INDEX(F1:F30,MATCH(INDEX(N1:Y10000,MATCH(TRIM(B40),Z1:Z10000,0),3),G1:G30,0))),INDEX(N1:Y10000,MATCH(TRIM(B40),Z1:Z10000,0),3),INDEX(F1:F30,MATCH(INDEX(N1:Y10000,MATCH(TRIM(B40),Z1:Z10000,0),3),G1:G30,0))&SI(HJ40="Secteur C-ville MTL"," CV","")))),0,0)

i get an error. actually im even unable to put this formula, excel refuses to accept it as a valid one, highlighting one of the trim functions. while the first one work just fine. all i added is - "if(iserror(" i the beginning and "),0,0)" at the end.

does anyone knows what causes this error?

p.s. just to make it a bit clearer the formula first gets a market
Code:
INDEX(N1:Y10000,MATCH(TRIM(B40),Z1:Z10000,0),3)

then looks for values depending on the market, hence the 2 indexes.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
sometimes a number, sometimes a text. could be anything. :)

Now, that's great:biggrin:...

An option...

Using a two-cell approach (faster processing)...

B2:

=MATCH(TRIM(B40),Z1:Z10000,0)

A2:
Code:
=IF(B40=0,"",
    IF(ISNUMBER(MATCH(INDEX(N1:Y10000,b2,3),G1:G30,0)),
     INDEX(F1:F30,MATCH(INDEX(N1:Y10000,b2,3),G1:G30,0))&
        IF(HJ40="Secteur C-ville MTL"," CV","")
     INDEX(N1:Y10000,b2,3)))

Another option is to introduce Longre's SETV/GETV pair from his morefunc.xll add-in.
 
Upvote 0
hey, here is the update... as expected your formula did work, but i just could not add any additional columns since they are all used (yeah, OMG, ALL 258 Columns used !!!!!), thus i had to do the following:

Code:
=IF(B40=0,"",IF(ISERROR(INDEX(MediaEn,MATCH(INDEX(Inventaire,MATCH(TRIM(B40),InventaireColonneRecherche,0),3),MediaFr,0))),IF(ISERROR(INDEX(Inventaire,MATCH(TRIM(B40),InventaireColonneRecherche,0),3)),IF(OR(IH40=0,IH40=""),"",IH40),INDEX(Inventaire,MATCH(TRIM(B40),InventaireColonneRecherche,0),3)),INDEX(MediaEn,MATCH(INDEX(Inventaire,MATCH(TRIM(B40),InventaireColonneRecherche,0),3),MediaFr,0))&IF(HJ40="Secteur C-ville MTL"," CV","")))

Basically, i put the IsError function inside the formula (in the middle index/match), instead of wrapping the whole thing. this way it didnt give me any errors and i managed to have it all done. definitely not an elegant solution. definitely needs additional column so that the number of calculations is reduced, but as of now, that is not possible, so i guess i'll stick with this for now :)

THANKS AGAIN !!! as always your help was hugely appreciated :D

p.s. **** its long to convert from french excel formulae to english manually :)
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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