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

 

[Mindpsyche]

Another possible solution, although the approach is very different and I'm not sure if your file size will reduce. Is it possible to break down the data into separate tables / datasheets with individual lookups? And then for your final output you can incorporate those looksups in an if function which should result in a regular size formula.

The good thing with this approach is that someone else having access can easily understand the layout of your data and make changes if necessary.

 

[Dannyh1]

Hi

Does it have to be formula as opposed to code?

*edit* sorry yes it does, just re-read post. I would suggest that you insist on code for this though as it would be easier to write operating guide for small bit of code than it would to explain this formula.

 

[J.Ty.]

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)))

Indeed, but this solution is not very efficient: all 6 LOOKUPs are always evaluated. Using IFERROR one can assure that the number of evaluated LOOKUPs is as low as possible.

J.Ty.

 

[Sal Paradise]

My understanding was that with BIGNUM it will evaluate the first non-error number (and therefore evaluate the absolute minimum amount of VLOOKUPs). My lack of understanding of how undocumented functions work I suppose.

 

[shg]

You can watch the formula evaluate.

 

[Sal Paradise]

How can I do that shg? I lack some basics apparently.

 

[J.Ty.]

Sal,

Select the cell with your formula, go to Formulas-> Evaluate Formula (icon with a magnifying glass with fx inside) and click. A small windown appears (why so small, Microsoft?) with your formula. With each click on the Evaluate button, you can watch how the subformulas of your formula turn into values. Very instructive, in particular for checking where the mistake is and where does the wrong result come from.

J.Ty.

 

[Sal Paradise]

Sal,

Select the cell with your formula, go to Formulas-> Evaluate Formula (icon with a magnifying glass with fx inside) and click. A small windown appears (why so small, Microsoft?) with your formula. With each click on the Evaluate button, you can watch how the subformulas of your formula turn into values. Very instructive, in particular for checking where the mistake is and where does the wrong result come from.

J.Ty.

That...is...awesome...

Wow.

You have made my entire week for me.

I owe you a beer if you're ever in Kansai ***an!

(What a language filter -- I cannot type the name of the country in which I reside. 2-letter ISO code JA, top-domain JP)