Formula too long

[dshafique]

Hi Guys! I am having an issue where my array formula is to long for the vba. I know that the limit is 255, but mine is 284+. I dont know how I can shrink it or parse it. Any help would be greatly appreciated.

the formula is

{=IFERROR(INDEX(pTable,MATCH(1,(pTable[[EMPLOYEE]:[EMPLOYEE]]=[@EMPLOYEE])*(pTable[[APPLICATION_NAME]:[APPLICATION_NAME]] = [@[APPLICATION_NAME]])*(pTable[[ACCESS LEVEL]:[ACCESS LEVEL]]=[@[ACCESS LEVEL]])*(pTable[[ENTITLEMENT NAME]:[ENTITLEMENT NAME]] = [@[ENTITLEMENT NAME]]),0),4),"")}

Thanks!

 

[Jonmo1]

Try writing the formula with standard range notation like A1:A100, instead of the table notation

 

[njimack]

or shorten the field names

 

[Jonmo1]

Also, the quotes at the end of the formula will be a problem when entering the formula with VBA.

You have to double those up..

change "" to """"

 

[dshafique]

That's actually not a bad idea @njimack

the macros is dynamic, so cant say the range will be the same for each one @Jonmo1

thanks guys!

 

[Jonmo1]

You can make it so that the formula is Not an array formula to begin with..

This is the part that requires it to be an array

MATCH(1,(range=criteria)*(range=criteria)*(range=criteria),0)

You can change that to

MATCH(1,INDEX((range=criteria)*(range=criteria)*(range=criteria),0),0)


This will no longer require the formula to be entered as an array, therefore not limited to 255.