- So if you're using Google Sheets
and you get that pound NA error,
we're going to talk about how that happens
and when it does, how to fix it.
(light music)
Okay, so the pound NA error, it stands for not available.
And all it's saying is, look,
there's nothing wrong with your formula.
There might even be nothing wrong with your data.
It's just that what you're looking for isn't available.
So this usually happens when you're
using the VLOOKUP formula,
just because that's the most common,
but it can also happen in HLOOKUP, LOOKUP, or MATCH.
And we'll spend most of our time using the VLOOKUP formula.
Look a little bit of MATCH at the end.
we'll start with an equal sign, VLOOKUP.
And the first thing that it wants is a search key.
So we're going to look for the value
that corresponds with E5.
So that's E5 is our short key,
and then we'll come over, specify the range.
So I'm going to hold down the shift key, go to the right,
hold down the control key, and go down.
You don't have to do that.
You can do that with your mouse, if you want.
And you need to fix the range also
for VLOOKUP to work the way you probably want it to.
So I'll hit F4,
and that's another way of just putting dollar signs
before all of the column and row references.
And the names are in the first column
and the states are in the second column.
So I wanna use a two as my index,
and then just end it with a false,
if you're working with unsorted data,
like I am, and we'll press enter.
Scroll back up here and there you go.
So this is a VLOOKUP formula.
It's working.
So we're not getting the error yet.
And what we're going to do is just break it
in a couple of different ways
and talk about how am I broken, how to fix it.
So in the meantime,
I'm going to put a few more values on here,
just so we can see it a little bit better.
Let's drag this formula down.
All right, so it's working right now.
So I'll go in here.
We're going to break it in the first way.
One of the most common ways,
I'm just going to put a little space.
So it's a hidden character, after the name in E5,
and I'll hit enter,
and here's what's happening here.
So the value in E5, when you look at that,
you can see that it's on the list.
So the value in E5 is in
E19.
So it should work, right?
There's no spelling error that you can see,
but what's happening here is that
there is a hidden character appended onto the end of this.
And this could happen
if you're getting your data from two different systems.
So let's say the data on the left is from HR
and some vacation tracking system.
And the data on the right is from payroll.
So the name's spelled right, but there's a space there.
One thing you could do is just go into the cell.
So I do a shortcut key to do that by using F2,
or you could double click in it.
And you can see that the cursor is
one to the right of the Y.
So something's in there.
So you just hit the backspace key, that fixes it.
But if you have a long list of data,
which if you're using VLOOKUP, you probably do,
let's talk about a more automated way to do this.
So if you want access to the spreadsheet
that has all of the errors in it,
and it has added notes to it,
and even the error message that's not a specific type,
so there's some information about that.
Follow the link in the description
or the link that's on your screen right now,
and you can get access to it.
And if you go to file, make a copy,
you can copy it into your Google Drive
and reference it whenever you run into one of these errors.
So let's put that space back in there.
So right now it's broken.
What you can use is the trim function.
So if I type equal and trim,
and then I go grab that value,
that function is going to take the spaces off of it.
So you would drag that function down,
fix all of your values.
And then when you're done,
you could copy this and right click,
paste special values to put it back where it was,
and the function works again.
So the trim function can clean up your data quite nicely,
and it could also do it,
so this could happen backwards.
Your source data could have an extra space in it,
and that's going to break it as well.
So you would use a trim function on the source data
or on the data that you're working with in the VLOOKUP.
Another problem that you can have with the VLOOKUP formula,
that will cause a not available error
is that you might not have specified the range right.
So if I go into F5 again,
and let's just say that I went to C90 instead,
actually that's still gonna grab this name
because this name is pretty high up,
but as an example, so I see it at 19,
let's just put this at 18,
and you get a not available error.
So that can be a little bit confusing
because you can look at it,
you can see that that value is available in the table.
So you think that something else is wrong,
but really, if you just look at the formula
and hit F2 you can see on the left that the range,
which is designated by the purple dotted rectangle,
just isn't going all the way down.
So this could happen if you're using shortcut keys
that designate the range
and you're holding down the control key and you hit down,
if there's a blank row in your data or multiple blank rows,
it's going to stop at the first blank row.
So you have to make sure your table is clean
and there's no empty rows in it,
and then when you use shortcut keys
it'll go all the way down.
So that's one way that the wrong range can cause an error.
The other way is just that,
well, that value isn't in the table.
So the VLOOKUP will run and it just won't find it.
And it'll cause a not available error that way.
So a lot of times a not available error can be correct.
It's just the lookup value's not there.
So let's change this back to what it was before.
And one last way that this can give an error
is if you specify the wrong parameter at the end.
So I always use false because the data
that I typically work with is not sorted,
but you can use true.
And then it will look through the data as if it's sorted.
So if your data sorted or unsorted,
you have to pick the right parameter
at the end the of VLOOKUP, the true or false.
So let's talk a little bit about how to handle this error.
So like we talked about before this error may be okay.
It might tell you it's not available.
And that tells you what you need to know,
and you just move on.
But if you want a different error message
or you don't want the error message at all,
there's a few different ways to handle this.
So what you would do,
you can wrap the function in the if error function.
So put the VLOOKUP inside of that,
but you can also use IFNA,
and they both do a similar thing,
but IFNA is just a little bit more specific.
So it's another type of error.
It won't catch it.
And that's maybe good if you're just looking for NA.
So let's just do IFNA,
and then we'll do the VLOOKUP.
It's going to be the same syntax.
I could have just copied and pasted it.
So I've done the VLOOKUP, and if it's NA,
what this does is, if it is NA then this will happen.
So I will say that the value is not available,
But customize that message for what's happening with you.
So it might be please check your spelling
if you know that most likely what they're doing,
the value is in the table or check for empty spaces.
But we're just going to say that,
you can say whatever you want,
but we're just going to say this value's not available,
and we'll close that off.
We'll come up and I forgot to specify the false.
So that's why that was firing
as if it was an error.
So I say false,
and it comes up with North Carolina
because there was no NA error.
Let's drag this down twice.
So these are working.
Let's break one of these functions.
So we'll just make the range really small,
like we did previously and hit enter.
And then you get a custom error message.
So it says this value is not available.
Now there may be times when you have a large table
and you do lookups and you just don't want to see the NAs,
you don't want a custom error message.
You just want nothing returned.
So what you could do,
you could go into this IFNA formula
and change that message,
just to two quotes with nothing inside of them,
and you hit enter, and it would just return nothing
if there was NA.
So you need to be a little bit careful with this,
if it's just a visual thing
and you just don't wanna see anything there,
that's good, but it is a non-number, but a blank cell.
There actually is something in that cell,
and it's not a number.
So you could be getting the num error,
which we talked about in a previous video.
So you could do something like you could return zero,
and then all the math operations that you're doing
on this table, it would work just fine with the zero.
Well, unless you're dividing by zero.
So again, all these situations are unique.
We're just giving you different options
on how to deal with it.
And one more formula that you can see this in,
so it can happen in VLOOKUP.
It can also happen with HLOOKUP and LOOKUP,
plain old vanilla LOOKUP.
It can also happen with MATCH.
So we'll do M-A-T-C-H.
And MATCH just indicates
the position of the item in the range.
So what we're looking for, so the search key,
we'll go up here and pick this person again,
and then we will specify the range,
and let's do the range incorrectly so you get the error,
and then we'll put a zero on the end
because the data's not sorted.
We'll close this off and you get a pound NA error
on that as well.
So the way you handle that error is exact same way
that you handle it with the VLOOKUP that we just went over.
So next we're going to talk about
how to record macros using Google Sheets,
and using macros you can automate your tasks
that you do over and over again.
So get up in the morning, click, press your macro,
and it would do the 20 steps that you do every morning,
but it would do it for you automatically.
So I'll see you in the next video, thanks.