avoid

Excel Magic Trick 990: VLOOKUP returns Zero and #N/A, What To Do?



Sharing buttons:

welcome to Excel magic trick number 990

hey if you want to download this

workbook 989 2 993 click on the link

below the video hey we have a great

vlookup video here we need to look up

the product name and return the ID now

there's two problems with this situation

some of the products don't have an ID so

vlookup when it looks up doublers for

example in season empty cell it's going

to return a 0 here not only that but

there's some products that are not in

our list the vlookup will return in n/a

what we really want instead of a 0 is no

ID and what we really want instead of an

A error is not in list so let's start

with vlookup the lookup value is going

to be the product name comma the table

I'm gonna highlight that range and hit

the f4 key to lock that comma the column

index well the product we're looking up

is in column 1 the ID we want to

retrieve and return to the cell is in

column 2 so you have to tell vlookup

that it is in column 2 comma true

approximate or false exact match we have

text here and although this column is

sorted we have items that are not in the

list which will cause a problem with

approximate match so we need to use

exact match and I'm going to use a zero

instead of false

alright control enter double click and

send it down so we see we have our zeros

and rnase we really want no ID and not

in list

well let's deal with the 0 first I'm

gonna highlight the vlookup because

we're gonna have to use that twice I'm

gonna say gif and vlookup is spitting

out that 0 so I'm and say hey vlookup

are you equal to 0 that's the logical

test part of if function true or false

comma the value if true well if it is 0

I want in double quotes no ID so text

and a formula requires those double

quotes that's the value of true the

value of false will I need to run that

vlookup again close parentheses control

enter this will deal with the zeros no

ID no ID but not the n/a now in 2007 and

later there's a great new function if

error it simply takes the thing that's

either delivering what we want or the

n/a if it's an n/a it says hey what do

you want me to show and we want to show

not in list and double quote close

parentheses control enter double click

and send it down so that's beautiful no

ID not unlist exactly what we want now

in 2003 or before I'm gonna copy this

loop that little piece right there now

if there is great because it allows us

to have a much shorter formula and not

run the vlookup a bunch like we did in

2003 or earlier so I'm going to say if

and the test here we have two tests

right is it an n/a is it zero so if and

I'm gonna say is na now I'm gonna

control V and I'm gonna cheat here I

didn't copy and paste this well what I

really need is inside the is n/a I need

to look at that vlookup so I'm going to

ctrl V and closed parenthesis they watch

this in a formula you can use alt enter

and it no I guess I didn't do anything

yeah it did it jump that down alt enter

so here we see that I'm in the logical

test argument there's the is n/a so I

need to put a close parenthesis right

there so all the is na is it's looking

at that vlookup in the logical test

it'll return a true if it is an N a

comma I need to tell it the value is

false comma not and

list n double quote that's the value of

two comma

otherwise I'm just going to run that

second if so the value if false it runs

the if you look up is equal to zero then

show no idea otherwise if you look up I

come to the N and I can close

parentheses

now that control-enter that alt-enter

actually added a line return in 2007 and

and later you can pull this down but

notice that that line return is there so

if you're looking up in the formula bar

sometimes it's deceiving I'm gonna click

up here and delete that line return I

did it just so it was easier to see over

here alright control-enter and double

click and send it down so there would

get the same result 2003 or earlier now

if you have a data set and you're not

allowed to alter the table meaning for

whatever reason you're required to keep

that cell empty this these solutions

will work but what if we could alter the

table whoops

you saw what we're gonna do and put no

ID but imagine you had a whole huge

table and you don't want to type those

individually I'm simply gonna highlight

that second column and I want to use the

go-to empty cell feature f5 opens go to

dialog box I'm going to click on special

and then no way

blanks that'll allow us to immediately

highlight all of the empty cells click

OK

absolutely beautiful now the active cell

is the light colored cell simply type no

ID to populate all of the highlighted

cells with the whatever it is that's in

the active cell control enter beautiful

now vlookup whoops now if you look up

equals vlookup I'm looking up that value

comma within this table it's not going

to return those zeros anymore f4 comma 2

comma 0 control enter double click and

send it down the only problem we have is

our n/a actually with that range still

high

in the act of Salem and hit the f2 key

I'm going to use 2000 and Excel 2007 or

later function if they're absolutely

awesome comma and then if it's an error

not an list control enter 2003 or before

we'd have to run the if and the vlookup

twice so I'm going to say equals if is

an a control V if that's an n/a then

that's the logical test for false comma

the value of true not in list otherwise

the value of false control V so we're

running the vlookup twice here we're

here we just ran at once

control enter double click and send it

down alright if we can alter the table

much small or formula to deal with zero

and the n/a if we can't alter the table

then maybe something like that alright

we'll see you next video