Daily F9 - 474 - Learn Excel from MrExcel Podcast

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on May 11, 2009.
**** 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.
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top