Insane formula -Need help simplifying

[Nicredible]

Hello Everyone,

This is my first post on Mr. Excel, so I hope I'm not doing anything incorrectly regarding this post.

So I work for a financial institution and I'm working on an asset reconciliation. The problem with this reconciliation is that we are comparing our holdings against multiple custodies, so my vlookup formulas are incredibly complicated. I have the spreadsheet working great but the only problem is that it's over 5 mb in size.

Please see the formula below, this is the reason the file is so large. I know I could solve this file size problem easily with VBA and macros, but I've been asked to complete this task using functions alone. I'm wondering if somebody here can help me simplify the logic behind this formula. Any help or suggestions would be GREATLY appreciated.

Again, this formula is huge, it tries to do a vlookup using a stock identifier, if it can't find the matching security with that identifier, it will try a different identifier (custodies and our system don't always have the same identifier), Then, if it can't find it with the alternate identifier, it will do the same check with another custody (so both possible identifiers on both custodies), Also, if it can't find a matching security using either of those methods it will consult a mapping table (for known securities that don't match), and repeat the process till a matching security is found.

Here is the formula:

=IF(ISNA(IF(ISNA(IF(ISNA(VLOOKUP(G4,CUSTODY1!$A$2:$T$3000,10,FALSE)),VLOOKUP(F4,CUSTODY1!$A$2:$T$3000,10,FALSE),VLOOKUP(G4,CUSTODY1!$A$2:$T$3000,10,FALSE))),IF(ISNA(VLOOKUP(G4,SCOTIACSTDY1!$D$2:$L$3000,6,FALSE)),VLOOKUP(F4,SCOTIACSTDY1!$D$2:$L$3000,6,FALSE),VLOOKUP(G4,SCOTIACSTDY1!$D$2:$L$3000,6,FALSE)),IF(ISNA(VLOOKUP(G4,CUSTODY1!$A$2:$T$3000,10,FALSE)),VLOOKUP(F4,CUSTODY1!$A$2:$T$3000,10,FALSE),VLOOKUP(G4,CUSTODY1!$A$2:$T$3000,10,FALSE)))),IF(ISNA(IF(ISNA(VLOOKUP(G4,CUSTODY1!$A$2:$T$3000,10,FALSE)),VLOOKUP(E4,CUSTODY1!$A$2:$T$3000,10,FALSE),VLOOKUP(G4,CUSTODY1!$A$2:$T$3000,10,FALSE))),IF(ISNA(VLOOKUP(G4,SCOTIACSTDY1!$D$2:$L$3000,6,FALSE)),VLOOKUP(E4,SCOTIACSTDY1!$D$2:$L$3000,6,FALSE),VLOOKUP(G4,SCOTIACSTDY1!$D$2:$L$3000,6,FALSE)),IF(ISNA(VLOOKUP(G4,CUSTODY1!$A$2:$T$3000,10,FALSE)),VLOOKUP(E4,CUSTODY1!$A$2:$T$3000,10,FALSE),VLOOKUP(G4,CUSTODY1!$A$2:$T$3000,10,FALSE))),IF(ISNA(IF(ISNA(VLOOKUP(G4,CUSTODY1!$A$2:$T$3000,10,FALSE)),VLOOKUP(F4,CUSTODY1!$A$2:$T$3000,10,FALSE),VLOOKUP(G4,CUSTODY1!$A$2:$T$3000,10,FALSE))),IF(ISNA(VLOOKUP(G4,SCOTIACSTDY1!$D$2:$L$3000,6,FALSE)),VLOOKUP(F4,SCOTIACSTDY1!$D$2:$L$3000,6,FALSE),VLOOKUP(G4,SCOTIACSTDY1!$D$2:$L$3000,6,FALSE)),IF(ISNA(VLOOKUP(G4,CUSTODY1!$A$2:$T$3000,10,FALSE)),VLOOKUP(F4,CUSTODY1!$A$2:$T$3000,10,FALSE),VLOOKUP(G4,CUSTODY1!$A$2:$T$3000,10,FALSE))))

I know, it's a lot to look at, but it DOES work. It's just not lean enough.

Thanks for looking, I'd appreciate ANY input!
Nick

 

[shg]

That may be the most resource-intensive formula I've seen -- the linear lookups are all repeated mutiple times:

=IF(ISNA(IF(ISNA(IF(ISNA(VLOOKUP(G4, CUSTODY1!$A$2:$T$3000,     10, FALSE)),  
                         VLOOKUP(F4, CUSTODY1!$A$2:$T$3000,     10, FALSE), 
                         VLOOKUP(G4, CUSTODY1!$A$2:$T$3000,     10, FALSE))), 
                 IF(ISNA(VLOOKUP(G4, SCOTIACSTDY1!$D$2:$L$3000,  6, FALSE)), 
                         VLOOKUP(F4, SCOTIACSTDY1!$D$2:$L$3000,  6, FALSE), 
                         VLOOKUP(G4, SCOTIACSTDY1!$D$2:$L$3000,  6, FALSE)), 
                 IF(ISNA(VLOOKUP(G4, CUSTODY1!$A$2:$T$3000,     10, FALSE)), 
                         VLOOKUP(F4, CUSTODY1!$A$2:$T$3000,     10, FALSE), 
                         VLOOKUP(G4, CUSTODY1!$A$2:$T$3000,     10, FALSE)))), 
         IF(ISNA(IF(ISNA(VLOOKUP(G4, CUSTODY 1!$A$2:$T$3000,    10, FALSE)), 
                         VLOOKUP(E4, CUSTODY1!$A$2:$T$3000,     10, FALSE), 
                         VLOOKUP(G4, CUSTODY1!$A$2:$T$3000,     10, FALSE))), 
                 IF(ISNA(VLOOKUP(G4, SCOTIACSTDY1!$D$2:$L$3000,  6, FALSE)), 
                         VLOOKUP(E4, SCOTIACSTDY1!$D$2:$L$3000,  6, FALSE), 
                         VLOOKUP(G4, SCOTIACSTDY1!$D$2:$L$3000,  6, FALSE)), 
                 IF(ISNA(VLOOKUP(G4, CUSTODY1!$A$2:$T$3000,     10, FALSE)), 
                         VLOOKUP(E4, CUSTODY1!$A$2:$T$3000,     10, FALSE), 
                         VLOOKUP(G4, CUSTODY1!$A$2:$T$3000,     10, FALSE))), 
         IF(ISNA(IF(ISNA(VLOOKUP(G4, CUSTODY1!$A$2:$T$3000,     10, FALSE)), 
                         VLOOKUP(F4, CUSTODY1!$A$2:$T$3000,     10, FALSE), 
                         VLOOKUP(G4, CUSTODY1!$A$2:$T$3000,     10, FALSE))), 
                 IF(ISNA(VLOOKUP(G4, SCOTIACSTDY1!$D$2:$L$3000,  6, FALSE)), 
                         VLOOKUP(F4, SCOTIACSTDY1!$D$2:$L$3000,  6, FALSE), 
                         VLOOKUP(G4, SCOTIACSTDY1!$D$2:$L$3000,  6, FALSE)), 
                 IF(ISNA(VLOOKUP(G4, CUSTODY1!$A$2:$T$3000,     10, FALSE)), 
                         VLOOKUP(F4, CUSTODY1!$A$2:$T$3000,     10, FALSE), 
                         VLOOKUP(G4, CUSTODY1!$A$2:$T$3000,     10, FALSE))))

I'm not going through the self-flagellation of figuring that out, but instead of

=if(isna(expression), somethingelse, expression)

you can use

=iferror(expression, somethingelse)

and nest it like so

=iferror(expression1, iferror(expression2, iferror(expression3, somethingelse)))

and it returns the first value that doesn't evaluate to an error, evluating each only once. That would cut the length in half.

EDIT: Or, you could put match formulas in helper columns, and reduce the load by maybe 90%.

 

[pplstuff]

One suggestion, to make it more readable, use the not() function. So, instead of =if(isna(if(isna(if(isna(... it would be =if(not(isna(),_true_,if(not(isna(),__true__,... This would help to break it down and be more readable and realistic to audit.

Another Suggestion, break it down into multiple helper cells, and reference those cells -- once again, this will make it more readable and easier to audit (although it will increase the workbook size, you'll have to decide which is of greater importance (size or neatness)).

Finally, perhaps use index(match()) technique instead of vlookup. If you're doing thousands of vlookups it will do two thing: 1) slow down calculation time, 2) increase workbook size.

Sorry I don't have an exact solution -- but, I hope the suggestions are of some value. Good luck.

 

[TheVillageIdiot]

I second the suggestion to junk the vlookup in favor of Index/Match.

Also, I would used named ranges at first to try to decipher the formula. You can then keep them or discard them on the rewrite.

 

[shg]

Maybe something like this (a miracle if correct):

=IFERROR(VLOOKUP(G4, CUSTODY1!$A$2:$T$3000,     10, FALSE),
 IFERROR(VLOOKUP(F4, CUSTODY1!$A$2:$T$3000,     10, FALSE),
 IFERROR(VLOOKUP(G4, SCOTIACSTDY1!$D$2:$L$3000,  6, FALSE),
 IFERROR(VLOOKUP(F4, SCOTIACSTDY1!$D$2:$L$3000,  6, FALSE),
 IFERROR(VLOOKUP(E4, CUSTODY1!$A$2:$T$3000,     10, FALSE),
 IFERROR(VLOOKUP(E4, SCOTIACSTDY1!$D$2:$L$3000,  6, FALSE),
 IFERROR(VLOOKUP(F4, SCOTIACSTDY1!$D$2:$L$3000,  6, FALSE), "I give up!")))))))

 

[Nicredible]

Wow, Thanks guys! all of these suggestions are great! obviously it's going to take some time to incorporate any of them, so I'll get back to you guys to let you know how it all worked out a little later. And if you happen to think of something else, I'm definately open to other suggestions as well.

Thanks again!
Nick

 

[sitewolf]

iferror only works in 2007+ doesn't it? not 2003-

 

[shg]

That's correct, it was introduced in Excel 2007.

 

[J.Ty.]

I think I understand the formula. So the question to the original poster is if this understanding is correct. I believe it is so, because my explanation seems to be quite simple and coherent.

Let Range1 be CUSTODY1!$A$2:$T$3000
Let Range2 be SCOTIACSTDY1!$D$2:$L$3000

Consider formulas

=VLOOKUP(G4;Range1;10;FALSE)
=VLOOKUP(F4;Range1;10;FALSE)
=VLOOKUP(E4;Range1;10;FALSE)
=VLOOKUP(G4;Range2;6;FALSE)
=VLOOKUP(F4;Range2;6;FALSE)
=VLOOKUP(E4;Range2;6;FALSE)

Then the result of the insane formula can be expressed as follows:

Evaluate those formulas and return the result of the first one which returns non-error and error if all of them return errors.


Best,

J.Ty.

 

[Sal Paradise]

If that's the case J. Ty., then a nice BigNum application could work to really cut down the size.

=LOOKUP(9.99E+307,CHOOSE({1,2,3,4,5,6,7},"No Match",VLOOKUP(G4;Range1;10;FALSE),VLOOKUP(F4;Range1;10;FALSE),VLOOKUP(E4;Range1;10;FALSE),VLOOKUP(G4;Range2;6;FALSE),VLOOKUP(F4;Range2;6;FALSE),VLOOKUP(E4;Range2;6;FALSE)))