Yet Another #N/A Dilemma - Help

[PrairieWriter]

I have searched the forum repeatedly and I have been unable to find
definitive solution. Like many others posting to this forum, I woul
like to remove the #N/A and replace it with 0. My formula reads
=VLOOKUP('ABM Identification'!CI$19,Camera_Code,2,0). When I replace i
with the formula =isna(VLOOKUP(AB
Identification'!CI$19,Camera_Code,2,0,FALSE),"",VLOOKUP(AB
Identification'!CI$19,Camera_Code,2,0,FALSE)) I get an error message
Any thoughts on how I might correct this problem? Thanks in advance

 

[Frank Kabel]

Hi
you already got an answer for this
=IF(isna(VLOOKUP(ABM
Identification'!CI$19,Camera_Code,2,0,FALSE),"",VLOOKUP(ABM
Identification'!CI$19,Camera_Code,2,0,FALSE))

 

[Frank Kabel]

Hi
just add a missing bracket:
=IF(isna(VLOOKUP(ABM
Identification'!CI$19,Camera_Code,2,0,FALSE)),"",VLOOKUP(ABM
Identification'!CI$19,Camera_Code,2,0,FALSE))

 

[Max]

Very close ..
Only the IF is missing in the expression
=IF(ISNA(VLOOKUP(..),"",VLOOKUP(..))

Try:

=IF(ISNA(VLOOKUP(ABM
Identification'!CI$19,Camera_Code,2,0,FALSE),"",VLOOKUP(ABM
Identification'!CI$19,Camera_Code,2,0,FALSE))

 

[Max]

Oops, one missing closing bracket ..
[ same as Frank's oops .. <g> ]

Corrected:

=IF(ISNA(VLOOKUP(..)),"",VLOOKUP(..))

 

[Lift Off]

Franks correct. Made the same mistake as me. Missing the bracket. Se
my Lift Off posting. :

 

[Max]

Yes, there was a missing bracket oops in my earlier reply ..

but .. the IF *was* missing from the original post here, viz.:

When I replace it
with the formula =isna(VLOOKUP(ABM

Apparently the original post was subsequently
edited by the OP in Excelforum to include the IF,
but the edited version doesn't show up here in the newsgroup ..

Not sure whether Excelforum posters are aware of this ..
The usual way to make corrections to an earlier post sent
is to post the corection as a reply to the original post

 

[PrairieWriter]

Thanks for all the (quick) suggestions. I’ve tried them all and I a
still not getting correct results. As I stated in my first plea fo
help I am trying to replace the #N/A value with 0. In the firs
worksheet (ABM Identification) the end user selects a value from a dro
down list, which is converted to a corresponding number in a secon
worksheet “hidden” in the background. When I run the script no value i
returned – not #N/A or 0. My script now reads:

=IF(ISNA(VLOOKUP('AB
Identification'!CI$19,Camera_Code,2,0)),"",VLOOKUP('AB
Identification'!CI$19,Camera_Code,2,0))

Thoughts

 

[Frank Kabel]

Hi
use
=IF(ISNA(VLOOKUP('ABMIdentification'!CI$19,Camera_Code,2,0)),0,VLOOKUP(
'ABMIdentification'!CI$19,Camera_Code,2,0))

 

[PrairieWriter]

Frank - IT WORKS!! Thanks so much for your swift response. Now all yo
have to do is solve the problem I am having copying a formula down
column. I want the cell reference number to stay the same and hav
indicated this with an absolute ($). When I copy down the formul
remains D$19 and does not change to E$19. When I copy to the right, th
formula changes to E$19. Any thoughts on how to solve this one?

Thanks again to everyone and Frank I owe you a pint regardless if yo
manage to solve my second problem....

P

 

[Frank Kabel]

Hi
the column index only changes automatically if you copy across columns
(e.g. to the right). But you want to change the column index while
copying across rows.

you may try the following (assumption: this formula starts in row 1):
=IF(ISNA(VLOOKUP(OFFSET('ABMIdentification'!CI$19,0,ROW()-1),Camera_Cod
e,2,0)),0,VLOOKUP(
OFFSET('ABMIdentification'!CI$19,0,ROW()-1),Camera_Code,2,0))

if you start in a different row change '-1' to '-#of_starting_row'

 

[Don]

I use an IF statement + ISNA function with my VLOOKUP
function. For example, IF(ISNA(VLOOKUP(FUNCTION),0,VLOOKUP
(FUNCTION) where FUNCTION is your particular lookup_value,
table_array, col_index). Hope this helps.

 

[PrairieWriter]

Just a short note to thank all those who responded to my recent inquiry
Your assistance allowed me to get my spreadsheet working properly

 

[iujfalvi]

Hi,

I have tried the indicated formula to eliminate #N/A from my table
based on the provided solutions.

as below

=IF(ISNA(VLOOKUP(A4;'G:\[Export.xls]Expor
All_Rel'!$D:$J;7;FALSE),"",VLOOKUP(A4;'G:\[Export.xls]Expor
All_Rel'!$D:$J;7;FALSE))

However excel 2000 returns me an error indicating the part

,"",VLOOKUP

Thanks in advance for any idea


Steve
Hungary

(e-mail address removed)

 

[Max]

Think you missed out a closing " ) " parentheses for the ISNA(VLOOKUP(...))
part

Here's the amended formula for you to try out (just copy and paste as-is)
[ it's hopefully without any line wrap ]

=IF(ISNA(VLOOKUP(A4;'G:\[Export.xls]ExportAll_Rel'!$D:$J;7;FALSE))
,"",VLOOKUP(A4;'G:\[Export.xls]ExportAll_Rel'!$D:$J;7;FALSE))