It’s in there! – Nesting

Nesting is not a formula or function. What it means in Excel is that you can include a formula within a formula. You are “nesting” a formula within a different formula. The best way to explain this is to give you a visual representation. We’ll look at nesting a TRIM function  within a VLOOKUP formula, but remember that there are many formulas that can be nested within each other (I have nested VLOOKUP formulas within other VLOOKUP formulas!) Generally, if you need to perform a separate function to get what you need for a different formula, it can be nested.

Let’s say we have a list of State Capitals (just as we did for our TRIM function post) and we want to find each capital’s state.

If you simply created the VLOOKUP with the data as is (with extra spaces) it would look like this and you would get an #N/A error: =VLOOKUP(A1,$D$1:$E$16,2,FALSE).

So what you want to do is add in the TRIM function within that formula. As you see from our original TRIM blog-post, the formula is =TRIM(A1). Therefore by “nesting” the two the VLOOKUP would now look like this: =VLOOKUP(TRIM(A1),$D$1:$E$16,2,FALSE).

 

If you remember, the first part of the VLOOKUP formula is “What you’re looking up”. In this case, we’re looking up the result of TRIM(A1) formula. I hope this makes sense... If you need further clarification, please post a comment below.

Keep Excelling!


Do you like this post? Comment below and / or share on Facebook or Twitter!