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

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" wrote:

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

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" wrote:

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" wrote:

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

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" wrote:

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" wrote:

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