Leave cell data (IF(ISNA(VLOOKUP) is TRUE instead of returning blank (" ") or #NA

Hi:
When I use this formula -

IF(ISNA(VLOOKUP(D9,$A$2:$B$15,2,FALSE)),,(VLOOKUP(D9,$A$2:$B$15,2,FALSE)))
The cell return is 0.

IF(ISNA(VLOOKUP(D9,$A$2:$B$15,2,FALSE)),"
",(VLOOKUP(D9,$A$2:$B$15,2,FALSE)))
The cell return is BLANK.

What I want is for the current cell data to remain, if the
"IF<ISNA<VLOOKUP" function is TRUE instead of the notorious #NA, Blank,
and "0" returns.

What should I do?
Please help.
Thanks,
Jay

Hi

What do you mean with 'What I want is for the current cell data to remain,
....'?

You can have in cell either a value or a formula. And the formula returns a
value only to cell where it resides. To make it short - whenever you enter a
formula into some cell, the previous entry there is lost after first
character is typed into!


Arvi Laanemets


<[email protected]> wrote in message
news:[email protected]...
> Hi:
> When I use this formula -
>
> IF(ISNA(VLOOKUP(D9,$A$2:$B$15,2,FALSE)),,(VLOOKUP(D9,$A$2:$B$15,2,FALSE)))
> The cell return is 0.
>
> IF(ISNA(VLOOKUP(D9,$A$2:$B$15,2,FALSE)),"
> ",(VLOOKUP(D9,$A$2:$B$15,2,FALSE)))
> The cell return is BLANK.
>
> What I want is for the current cell data to remain, if the
> "IF<ISNA<VLOOKUP" function is TRUE instead of the notorious #NA, Blank,
> and "0" returns.
>
> What should I do?
> Please help.
> Thanks,
> Jay
>

The current cell data is the result of the formula, ant previous value has
been over-written.

--

HTH

RP
(remove nothere from the email address if mailing direct)


<[email protected]> wrote in message
news:[email protected]...
> Hi:
> When I use this formula -
>
> IF(ISNA(VLOOKUP(D9,$A$2:$B$15,2,FALSE)),,(VLOOKUP(D9,$A$2:$B$15,2,FALSE)))
> The cell return is 0.
>
> IF(ISNA(VLOOKUP(D9,$A$2:$B$15,2,FALSE)),"
> ",(VLOOKUP(D9,$A$2:$B$15,2,FALSE)))
> The cell return is BLANK.
>
> What I want is for the current cell data to remain, if the
> "IF<ISNA<VLOOKUP" function is TRUE instead of the notorious #NA, Blank,
> and "0" returns.
>
> What should I do?
> Please help.
> Thanks,
> Jay
>

Ok. Maybe I should clarify.

My requirement is -
I have two spreadsheets (SS1 & SS2) with, say, two columns (A & B)
each.

My source list is SS1-Columns A&B.
My target list is SS2-Columns A&B.

I'd like to lookup items in target SS2-Col A in source SS1-Col A and if
there is a match
- REPLACE target SS2-Col B with source SS1- Col B.
If there is NO match
- Leave target SS2-Col B alone without replacing it with blank, 0 or
#NA.

I was typing the IF<ISNA<VLOOKUP formula (in previous email) in the
target SS2-Col B cell and expecting it to either REPLACE or retain the
previous value it had before the formula was typed - bummer.

Well, if I can't use this formula to do that, How could I do this?
- Thanks

Hi

This is exactly what we both did say. You can have in column SS2!B:B either
VLOOKUP formula, or previous value. But not both!

Maybe 3rd column will do for you - where the value from sheet SS1 is
displayed, when such is present, or the value from column B is displayed,
when no matching entry exists in SS1!A:A. Like
C2=IF(ISERROR(VLOOKUP(A2,SS1!$A$2:$B$100,2,0)),B2,VLOOKUP(A2,SS1!$A$2:$B$100
,2,0))
NB! You can't have this formula in column B !!!


Arvi Laanemets


"Jay" <[email protected]> wrote in message
news:[email protected]...
> Ok. Maybe I should clarify.
>
> My requirement is -
> I have two spreadsheets (SS1 & SS2) with, say, two columns (A & B)
> each.
>
> My source list is SS1-Columns A&B.
> My target list is SS2-Columns A&B.
>
> I'd like to lookup items in target SS2-Col A in source SS1-Col A and if
> there is a match
> - REPLACE target SS2-Col B with source SS1- Col B.
> If there is NO match
> - Leave target SS2-Col B alone without replacing it with blank, 0 or
> #NA.
>
> I was typing the IF<ISNA<VLOOKUP formula (in previous email) in the
> target SS2-Col B cell and expecting it to either REPLACE or retain the
> previous value it had before the formula was typed - bummer.
>
> Well, if I can't use this formula to do that, How could I do this?
> - Thanks
>

Arvi:
Your suggestion to place the formula in SS2:Col 3 worked like a charm
except that when I drag the formula down the row, the cells
automatically get written with the current value in Col B.

The matching value from SS1Col B is written ONLY if I do a search in
that spreadsheet to look for SS2ColA value - defeating the whole
purpose!

Please help!
Thanks, Jay

Hi

Post your formula from some cell here - p.e. the one working, and the nex
one (not working). Probably your range references are wrong (relative vs.
absolute reference)


Arvi Laanemets


"Jay" <[email protected]> wrote in message
news:[email protected]...
> Arvi:
> Your suggestion to place the formula in SS2:Col 3 worked like a charm
> except that when I drag the formula down the row, the cells
> automatically get written with the current value in Col B.
>
> The matching value from SS1Col B is written ONLY if I do a search in
> that spreadsheet to look for SS2ColA value - defeating the whole
> purpose!
>
> Please help!
> Thanks, Jay
>