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

[joisimha]

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

 

[Arvi Laanemets]

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

 

[Bob Phillips]

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)

 

[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

 

[Arvi Laanemets]

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]

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

 

[Arvi Laanemets]

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