Nesting formulae

[Locopete99]

Whats the most levels of nesting you can have on Excel 2010?

[ARGK]

Hi
There is no technical limit but I am sure there would be a feasible limit. :-).
Tony

[Tony Valko]

64

[Locopete99]

Thanks

I thought it was something stupidly high like that but my excel keeps telling me off for using more than about 19 if i'm calculating it correct:


"IF(COUNTIF(H59,"*"&VLOOKUP(B59,$AC$10:$AK$109,7,FALSE)&"*"),VLOOKUP(B59,$AC$10:$AI$111,7,FALSE),IF(COUNTIF(H59,"*"&VLOOKUP(B59,$AC$10:$AK$109,8,FALSE)&"*"),VLOOKUP(B59,$AC$10:$AK$109,8,FALSE),IF(COUNTIF(H59,"*"&VLOOKUP(B59,$AC$10:$AK$109,9,FALSE)&"*"),VLOOKUP(B59,$AC$10:$AK$109,9,FALSE),IF(COUNTIF(H59,"*"&VLOOKUP(B59,$AC$10:$AL$109,10,FALSE)&"*"),VLOOKUP(B59,$AC$10:$AL$109,10,FALSE),IF(COUNTIF(H59,"*"&VLOOKUP(B59,$AC$10:$AM$109,11,FALSE)&"*"),VLOOKUP(B59,$AC$10:$AM$109,11,FALSE)," ")))))"

[Tony Valko]

What's in B59 and H59?

[Locopete99]

B59 is basically the same as a primary key in access. It is a number string that multiple cells use to reference against another table.


H59 has a text string. So the vlookup for H59 checks to see if it contains a certain text string.

My sheet is a bit big to up load on here.


This is to do with my work, so it is all to do with part numbers for fittings.

I have one vlookup table that has;

identifier, product family, lamp type 1/ lamp type 2/ lamp type 3.

So the lookups are saying:

If Cell H59 has one of the same lamps as in the reference, then enter the lamp type that it matches. and this checks 3-5 different lamps

[Tony Valko]

My sheet is a bit big to up load on here.

Can you make up a SMALL sample file that demonstrates what you're wanting to do? About 20 rows worth of data should be plenty.

It looks like we only need to see columns B, H, AC:AK. All other columns would be irrelavant and just add "clutter" to the sample file.

[Locopete99]

I've attached the smallest version I can that gives you an idea of what im trying to do

[Tony Valko]

Eh, that's too big for me.

I have a personal rule where I won't look at files that are >50kb.

Maybe someone else will have a look.

[Locopete99]

No problem, I cant keep the file up as its company rules.
I'm sure i'll find a way to do it.

[Tony Valko]

Instead of posting an actual work file just make up a SMALL file with fake made up dummy data that demonstrates what you want to do.