Vlookup-avoiding "N/A"

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I am trying to do a vlookup with and without concatenate.

I have a list of radio stations in column A.
KBCE
KOZT
WCBE

Going across each column is a seperate week.
B= 9/13/03 C= 9/22/03 D= 9/29/03 E= 10/6/03

In each week there will be numbers and other values ie,
2,4, add, n2,etc.

For the most current week, which in this case is column E
(each week the info is bumped one week to the left to
clear E for the new week.), I concatenate the Vlookup
because there are two columns of data I need to reference
for the current week.

The destination worksheet will always have the same list
of stations each week however, the import sheet doesn't
have all the stations on it each week.

For example, my stations I listed above.....
Those three will be on my destination sheet each week.
KBCE
KOZT
WCBE

But, the import sheet one week may not have KBCE, but the
next week it will, then another station or two may be not
on the following week.

I used a formula :
=IF(VLOOKUP($A58,'[ImportTracking.xls]Janis Joplin'!
$A$48:$F$70,4,FALSE),"",VLOOKUP($A58,'[ImportTracking.xls]
Janis Joplin'!$A$48:$F$70,4,FALSE))

To say if the field was blank then return blank, but if
the station is not on the import sheet at all then the
formula returns "N/A".

How do I return blank if the station is not on the list?

Thanks greatly, in advance.
 
Try this:

=if(iserror(IF(VLOOKUP($A58,'[ImportTracking.xls]Janis
Joplin'!
$A$48:$F$70,4,FALSE),"",VLOOKUP($A58,'[ImportTracking.xls]
Janis Joplin'!$A$48:$F$70,4,FALSE))," ",IF(VLOOKUP
($A58,'[ImportTracking.xls]Janis Joplin'!
$A$48:$F$70,4,FALSE),"",VLOOKUP($A58,'[ImportTracking.xls]
Janis Joplin'!$A$48:$F$70,4,FALSE)))
 
Excel is telling me that the formula contains an error.
-----Original Message-----
Try this:

=if(iserror(IF(VLOOKUP($A58,'[ImportTracking.xls]Janis
Joplin'!
$A$48:$F$70,4,FALSE),"",VLOOKUP ($A58,'[ImportTracking.xls]
Janis Joplin'!$A$48:$F$70,4,FALSE))," ",IF(VLOOKUP
($A58,'[ImportTracking.xls]Janis Joplin'!
$A$48:$F$70,4,FALSE),"",VLOOKUP ($A58,'[ImportTracking.xls]
Janis Joplin'!$A$48:$F$70,4,FALSE)))

-----Original Message-----
I am trying to do a vlookup with and without concatenate.

I have a list of radio stations in column A.
KBCE
KOZT
WCBE

Going across each column is a seperate week.
B= 9/13/03 C= 9/22/03 D= 9/29/03 E= 10/6/03

In each week there will be numbers and other values ie,
2,4, add, n2,etc.

For the most current week, which in this case is column E
(each week the info is bumped one week to the left to
clear E for the new week.), I concatenate the Vlookup
because there are two columns of data I need to reference
for the current week.

The destination worksheet will always have the same list
of stations each week however, the import sheet doesn't
have all the stations on it each week.

For example, my stations I listed above.....
Those three will be on my destination sheet each week.
KBCE
KOZT
WCBE

But, the import sheet one week may not have KBCE, but the
next week it will, then another station or two may be not
on the following week.

I used a formula :
=IF(VLOOKUP($A58,'[ImportTracking.xls]Janis Joplin'!
$A$48:$F$70,4,FALSE),"",VLOOKUP ($A58,'[ImportTracking.xls]
Janis Joplin'!$A$48:$F$70,4,FALSE))

To say if the field was blank then return blank, but if
the station is not on the import sheet at all then the
formula returns "N/A".

How do I return blank if the station is not on the list?

Thanks greatly, in advance.
.
Click to expand...
.
Click to expand...
 
This suggestion also results in "the forumla you typed
contains an error".

-----Original Message-----
Hi, I think you can work in the ISNA function and that should do it.


I am trying to do a vlookup with and without concatenate.

I have a list of radio stations in column A.
KBCE
KOZT
WCBE

Going across each column is a seperate week.
B= 9/13/03 C= 9/22/03 D= 9/29/03 E= 10/6/03

In each week there will be numbers and other values ie,
2,4, add, n2,etc.

For the most current week, which in this case is column E
(each week the info is bumped one week to the left to
clear E for the new week.), I concatenate the Vlookup
because there are two columns of data I need to reference
for the current week.

The destination worksheet will always have the same list
of stations each week however, the import sheet doesn't
have all the stations on it each week.

For example, my stations I listed above.....
Those three will be on my destination sheet each week.
KBCE
KOZT
WCBE

But, the import sheet one week may not have KBCE, but the
next week it will, then another station or two may be not
on the following week.

I used a formula :
=IF(VLOOKUP($A58,'[ImportTracking.xls]Janis Joplin'!
$A$48:$F$70,4,FALSE),"",VLOOKUP ($A58,'[ImportTracking.xls]
Janis Joplin'!$A$48:$F$70,4,FALSE))

To say if the field was blank then return blank, but if
the station is not on the import sheet at all then the
formula returns "N/A".

How do I return blank if the station is not on the list?

Thanks greatly, in advance.
Click to expand...


.
Click to expand...
 
As long as you have a working formula at the moment, then you shoudl be able to use something
like:-

=IF(ISNA(Your_Formula),"",Your_Formula)

Note that you do not include the = signs at the beginning of Your_Formula

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



Chris said:
I am trying to do a vlookup with and without concatenate.

I have a list of radio stations in column A.
KBCE
KOZT
WCBE

Going across each column is a seperate week.
B= 9/13/03 C= 9/22/03 D= 9/29/03 E= 10/6/03

In each week there will be numbers and other values ie,
2,4, add, n2,etc.

For the most current week, which in this case is column E
(each week the info is bumped one week to the left to
clear E for the new week.), I concatenate the Vlookup
because there are two columns of data I need to reference
for the current week.

The destination worksheet will always have the same list
of stations each week however, the import sheet doesn't
have all the stations on it each week.

For example, my stations I listed above.....
Those three will be on my destination sheet each week.
KBCE
KOZT
WCBE

But, the import sheet one week may not have KBCE, but the
next week it will, then another station or two may be not
on the following week.

I used a formula :
=IF(VLOOKUP($A58,'[ImportTracking.xls]Janis Joplin'!
$A$48:$F$70,4,FALSE),"",VLOOKUP($A58,'[ImportTracking.xls]
Janis Joplin'!$A$48:$F$70,4,FALSE))

To say if the field was blank then return blank, but if
the station is not on the import sheet at all then the
formula returns "N/A".

How do I return blank if the station is not on the list?

Thanks greatly, in advance.
Click to expand...
 
Does it ALWAYS say that, or only when something is missing?

If ISNA doesn't work there is always ISERROR, or both like

if(or(isna(big long formula),iserror(big long formula)),"",big long formula)


Chris said:
Excel is telling me that the formula contains an error.
-----Original Message-----
Try this:

=if(iserror(IF(VLOOKUP($A58,'[ImportTracking.xls]Janis
Joplin'!
$A$48:$F$70,4,FALSE),"",VLOOKUP ($A58,'[ImportTracking.xls]
Janis Joplin'!$A$48:$F$70,4,FALSE))," ",IF(VLOOKUP
($A58,'[ImportTracking.xls]Janis Joplin'!
$A$48:$F$70,4,FALSE),"",VLOOKUP ($A58,'[ImportTracking.xls]
Janis Joplin'!$A$48:$F$70,4,FALSE)))

-----Original Message-----
I am trying to do a vlookup with and without concatenate.

I have a list of radio stations in column A.
KBCE
KOZT
WCBE

Going across each column is a seperate week.
B= 9/13/03 C= 9/22/03 D= 9/29/03 E= 10/6/03

In each week there will be numbers and other values ie,
2,4, add, n2,etc.

For the most current week, which in this case is column E
(each week the info is bumped one week to the left to
clear E for the new week.), I concatenate the Vlookup
because there are two columns of data I need to reference
for the current week.

The destination worksheet will always have the same list
of stations each week however, the import sheet doesn't
have all the stations on it each week.

For example, my stations I listed above.....
Those three will be on my destination sheet each week.
KBCE
KOZT
WCBE

But, the import sheet one week may not have KBCE, but the
next week it will, then another station or two may be not
on the following week.

I used a formula :
=IF(VLOOKUP($A58,'[ImportTracking.xls]Janis Joplin'!
$A$48:$F$70,4,FALSE),"",VLOOKUP ($A58,'[ImportTracking.xls]
Janis Joplin'!$A$48:$F$70,4,FALSE))

To say if the field was blank then return blank, but if
the station is not on the import sheet at all then the
formula returns "N/A".

How do I return blank if the station is not on the list?

Thanks greatly, in advance.
.
Click to expand...
.
Click to expand...
Click to expand...
 
ok formula contains an error.. misinterpreted last time. adding in an ISNA
statement by itself wont result in "formula contains an error" unless it was
put in place incorrectly. it will be a very long formula, but should still
work nonetheless:

if(isna(your long formula),"",your long formula)

hope it helps.

This suggestion also results in "the forumla you typed
contains an error".

-----Original Message-----
Hi, I think you can work in the ISNA function and that should do it.


I am trying to do a vlookup with and without concatenate.

I have a list of radio stations in column A.
KBCE
KOZT
WCBE

Going across each column is a seperate week.
B= 9/13/03 C= 9/22/03 D= 9/29/03 E= 10/6/03

In each week there will be numbers and other values ie,
2,4, add, n2,etc.

For the most current week, which in this case is column E
(each week the info is bumped one week to the left to
clear E for the new week.), I concatenate the Vlookup
because there are two columns of data I need to reference
for the current week.

The destination worksheet will always have the same list
of stations each week however, the import sheet doesn't
have all the stations on it each week.

For example, my stations I listed above.....
Those three will be on my destination sheet each week.
KBCE
KOZT
WCBE

But, the import sheet one week may not have KBCE, but the
next week it will, then another station or two may be not
on the following week.

I used a formula :
=IF(VLOOKUP($A58,'[ImportTracking.xls]Janis Joplin'!
$A$48:$F$70,4,FALSE),"",VLOOKUP ($A58,'[ImportTracking.xls]
Janis Joplin'!$A$48:$F$70,4,FALSE))

To say if the field was blank then return blank, but if
the station is not on the import sheet at all then the
formula returns "N/A".

How do I return blank if the station is not on the list?

Thanks greatly, in advance.
Click to expand...


.
Click to expand...
Click to expand...
 
You should try (ISNA and put a parenthesis before the
comma before the "". See attached.
=IF(ISNA(VLOOKUP($A58,'[ImportTracking.xls]Janis Joplin'! $A$48:$F$70,4,FALSE)),"",VLOOKUP
Click to expand...
($A58,'[ImportTracking.xls]
Janis Joplin'!$A$48:$F$70,4,FALSE))
Click to expand...
-----Original Message-----
I am trying to do a vlookup with and without concatenate.

I have a list of radio stations in column A.
KBCE
KOZT
WCBE

Going across each column is a seperate week.
B= 9/13/03 C= 9/22/03 D= 9/29/03 E= 10/6/03

In each week there will be numbers and other values ie,
2,4, add, n2,etc.

For the most current week, which in this case is column E
(each week the info is bumped one week to the left to
clear E for the new week.), I concatenate the Vlookup
because there are two columns of data I need to reference
for the current week.

The destination worksheet will always have the same list
of stations each week however, the import sheet doesn't
have all the stations on it each week.

For example, my stations I listed above.....
Those three will be on my destination sheet each week.
KBCE
KOZT
WCBE

But, the import sheet one week may not have KBCE, but the
next week it will, then another station or two may be not
=IF(VLOOKUP($A58,'[ImportTracking.xls]Janis Joplin'!
Click to expand...
$A$48:$F$70,4,FALSE),"",VLOOKUP($A58,'[ImportTracking.xls]
Janis Joplin'!$A$48:$F$70,4,FALSE))
Click to expand...
 
Back
Top