# have i reached the maximum number of nested functions?

#### hobgoblinvasya

##### Board Regular
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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

##### MrExcel MVP
What result is the formula expected to return - a number or text?

#### hobgoblinvasya

##### Board Regular
sometimes a number, sometimes a text. could be anything.

##### MrExcel MVP
sometimes a number, sometimes a text. could be anything.

Now, that's great...

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.

#### hobgoblinvasya

##### Board Regular
thanks man !

i'll definitely take a look at your code tomorrow and keep you posted

##### MrExcel MVP
thanks man !

i'll definitely take a look at your code tomorrow and keep you posted

Note that I did not add the test

IF(ISNUMBER(B2),...

You'll need that.

#### hobgoblinvasya

##### Board Regular
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

Replies
5
Views
774
Replies
10
Views
2K
Replies
1
Views
683
Replies
18
Views
4K
Replies
1
Views
2K

1,191,555
Messages
5,987,257
Members
440,086
Latest member
Mahi786

### 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.

### Which adblocker are you using?

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

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