**** Kusleika is our guest podcaster today. **** is the host of the Daily Dose of Excel. He shows how to troubleshoot formulas by evaluating parts of a formula right in the formula bar. Episode 474 shows you how.
Transcript of the video:
Welcome back to the learn Excel podcast.
I'm Bill Jelen.
We're still here in Seattle.
I have ****, who's like a star of the daily dose of Excel podcast.
That's www.dailydoseofexcel.com.
He has a great Excel tip for you. Here's ****.
****: Today's tip is going to be about debugging formulas that return errors.
In cell F2, I have a formula It's a VLOOKUP formula and inside of that formula is a MATCH formula.
Because I've used false as the third argument from match and false has the fourth argument for VLOOKUP.
Either one of those formulas could return N/A, if a match isn't found.
As this formula returns N/A.
I'm not sure if it's the match or the vlookup, that's causing the problem.
So, here's how I'm going to find out I click in the formula bar to go to edit mode, and I highlight a portion of my formula.
In this case, I'm going to highlight the match portion.
Now, It's very important to get your parentheses aligned.
You can't highlight the opening parenthesis and not highlight the closing.
To evaluate the formula, I hit [ ctrl + = ] and It evaluates that portion of the formula and I can see that the match portion of my formula returns to the number 3.
The Match formula was supposed to return the column for quantity and in the range on the left, you can see that the column for quantity is 3.
So, it's not the match portion of my formula, that's causing the problem.
That only leaves the vlookup, but for illustrative purposes I can highlight the vlookup portion of my formula.
I'll hit control equals and I can see that that returns Na Again, I'll hit escape, so that I don't lose what I had.
It will kick me out of edit mode and return my formula back to what it was and I know that it was unable to find S80 in the range, that I've specified up.
On further inspection, I can see that my lookup value is 'S8' and a capital 'O'.
rather than S-80, a quick edit and [ Enter ] and I get the right answer.
That's how you can debug formulas that return errors. Thanks!
MrExcel: Wow! What a cool tip, using [ ctrl + enter ].
That's another one that I've learned.
Thanks ****, remember go to www.dailydoseofexcel.com for daily tips from **** and other MVPS.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
We're still here in Seattle.
I have ****, who's like a star of the daily dose of Excel podcast.
That's www.dailydoseofexcel.com.
He has a great Excel tip for you. Here's ****.
****: Today's tip is going to be about debugging formulas that return errors.
In cell F2, I have a formula It's a VLOOKUP formula and inside of that formula is a MATCH formula.
Because I've used false as the third argument from match and false has the fourth argument for VLOOKUP.
Either one of those formulas could return N/A, if a match isn't found.
As this formula returns N/A.
I'm not sure if it's the match or the vlookup, that's causing the problem.
So, here's how I'm going to find out I click in the formula bar to go to edit mode, and I highlight a portion of my formula.
In this case, I'm going to highlight the match portion.
Now, It's very important to get your parentheses aligned.
You can't highlight the opening parenthesis and not highlight the closing.
To evaluate the formula, I hit [ ctrl + = ] and It evaluates that portion of the formula and I can see that the match portion of my formula returns to the number 3.
The Match formula was supposed to return the column for quantity and in the range on the left, you can see that the column for quantity is 3.
So, it's not the match portion of my formula, that's causing the problem.
That only leaves the vlookup, but for illustrative purposes I can highlight the vlookup portion of my formula.
I'll hit control equals and I can see that that returns Na Again, I'll hit escape, so that I don't lose what I had.
It will kick me out of edit mode and return my formula back to what it was and I know that it was unable to find S80 in the range, that I've specified up.
On further inspection, I can see that my lookup value is 'S8' and a capital 'O'.
rather than S-80, a quick edit and [ Enter ] and I get the right answer.
That's how you can debug formulas that return errors. Thanks!
MrExcel: Wow! What a cool tip, using [ ctrl + enter ].
That's another one that I've learned.
Thanks ****, remember go to www.dailydoseofexcel.com for daily tips from **** and other MVPS.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
