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)))



Here is what I tried, but if the value is blank (missing) I am still getting
a 0

=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

Which of your lookup values may not be present, D5 or B10? Or both?


--
Biff
Microsoft Excel MVP


"JessM" wrote in message
...
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)))



Here is what I tried, but if the value is blank (missing) I am still
getting
a 0

=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