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!

Lose the Excess – TRIM Function

Are you trying to perform a VLOOKUP Formula but it’s not working? Even though you KNOW the formula is right AND you KNOW the data is there? The issue MAY just be that there are leading or trailing spaces that you don’t see. After all, in an Excel cell the words “Boston” and “Boston     ” LOOK identical, but they’re not (go ahead and try it, we’ll wait for you to come back!)

As I stated, they are not identical, and thus the formula will not find a match and return a “#N/A” error.

Getting rid of these spaces is as simple as typing in

 =TRIM(cell that contains extra spaces)

 

After removing the extra spaces, you may or may not see a difference; depending on where the spaces – leading spaces (before your data) or trailing spaces (after your data). Either way, this MAY be all that is needed to make your formula work.

Keep Excelling! 

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