Vlookups to return a N/A value

J

JessM

I am trying to write a formula that will return a N/A if the value in the
lookup table is missing (blank) or if the value does not exist in the table
at all. I am working with a monthly data set, and not all variables show up
every month and I want it to be returned as N/A.

I have the below two formulas that do this - but I need to figure out a way
to combine them together.

Formula that returns N/A if the value is missing altogether in the lookup
table:
=IF(ISERROR(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B10,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE)),"N/A",(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B10,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE)))

Formula that returns N/A if the value is blank in the lookup table:
=IF(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B95,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE)="","N/A",(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B95,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE)))

Thanks!
Jessica
 
X

xlmate

Not sure if this is what you want
try this on both formula

=(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B10,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),0))

=(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B95,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE))

Generally, Vlookup will return #N/A if the lookup value is missing, try
remove the
error handling part as above

Post back with a sample and the expected result if this is not what you are
after
HTH
--
Pls provide your feedback by clicking the Yes button below if this post have
help you. This will help others to search the archives for result better.


Thank You

cheers, francis
 
J

JessM

I don't want the formula to return a #N/A if it can't find it it the lookup
table- I want a N/A returned. I want to combine the two formulas into one
formula so it will be something like the below. However it is only working if
it is the error #N/A and not if the value is just missing (blank) in the look
up table. I am still getting a zero returned instead of N/A.

=IF(OR(ISERROR(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B10,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE)),VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B10,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE)=TRUE),"N/A",(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B10,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE)))

Thanks,
Jessica



xlmate said:
Not sure if this is what you want
try this on both formula

=(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B10,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),0))

=(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B95,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE))

Generally, Vlookup will return #N/A if the lookup value is missing, try
remove the
error handling part as above

Post back with a sample and the expected result if this is not what you are
after
HTH
--
Pls provide your feedback by clicking the Yes button below if this post have
help you. This will help others to search the archives for result better.


Thank You

cheers, francis









JessM said:
I am trying to write a formula that will return a N/A if the value in the
lookup table is missing (blank) or if the value does not exist in the table
at all. I am working with a monthly data set, and not all variables show up
every month and I want it to be returned as N/A.

I have the below two formulas that do this - but I need to figure out a way
to combine them together.

Formula that returns N/A if the value is missing altogether in the lookup
table:
=IF(ISERROR(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B10,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE)),"N/A",(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B10,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE)))

Formula that returns N/A if the value is blank in the lookup table:
=IF(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B95,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE)="","N/A",(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B95,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE)))

Thanks!
Jessica
Click to expand...
Click to expand...
 
J

JessM

That is not quite it. I don't want the error #N/A returned, I want N/A
returned. I was trying this formula - but it isn't working for the missing
values (blanks) in the lookup table it is till returning a "0" for them.

=IF(OR(ISERROR(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B10,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE)),VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B10,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE)=TRUE),"N/A",(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B10,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE)))

Thanks,
Jessica
xlmate said:
Not sure if this is what you want
try this on both formula

=(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B10,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),0))

=(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B95,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE))

Generally, Vlookup will return #N/A if the lookup value is missing, try
remove the
error handling part as above

Post back with a sample and the expected result if this is not what you are
after
HTH
--
Pls provide your feedback by clicking the Yes button below if this post have
help you. This will help others to search the archives for result better.


Thank You

cheers, francis









JessM said:
I am trying to write a formula that will return a N/A if the value in the
lookup table is missing (blank) or if the value does not exist in the table
at all. I am working with a monthly data set, and not all variables show up
every month and I want it to be returned as N/A.

I have the below two formulas that do this - but I need to figure out a way
to combine them together.

Formula that returns N/A if the value is missing altogether in the lookup
table:
=IF(ISERROR(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B10,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE)),"N/A",(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B10,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE)))

Formula that returns N/A if the value is blank in the lookup table:
=IF(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B95,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE)="","N/A",(VLOOKUP(D$5,[Meeting_Planner_Towers_Final.xls]Sheet1!$C:$HH,MATCH($B95,[Meeting_Planner_Towers_Final.xls]Sheet1!$C$1:$HH$1,0),FALSE)))

Thanks!
Jessica
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vlookup and If statement help 1
Help with Vlookup Match and IF statement 2
Help with If and Vlookup Statement 1
Help with IF and VLookup Match Statement 1
Scrabble Value calculation for Welsh words 0
combination IF- and VLOOKUP-function fails 2
Correct #N/A Value 6
Vlookup #n/a 6

Top