IF(ISNA(VLOOKUP(D2,I:I,1,FALSE)),"DONE", "")..Please save my 1

[scottian]

Im trying to use the above formula to determine whether a value from one column exists in another column, if the value doesnt exist then i want the cell to show the "DONE" else leave the cell blank. When i first cobbled this together, using advice from here and a test file, it worked. when i moved the formula to the actual file it stopped working.I know for fact that some values dont exist in the second column but its not picking up on this.
Im going nuts because i cant work out why. If anyone can help it'd be much appreciated.

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[GlennUK]

Give examples of the values please.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[KenWright]

How about:-

=IF(COUNTIF(I:I,D2),"DONE","")

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Fenrirshowl]

If it used to work but now doesn't for all values, I'd ask whether the workbook is set to manual calculation. Hit F9 and see what happens.

 

[SkipVought]

Hi,

I have deserted the use of VLOOKUP in favor of INDEX & MATCH, for several reasons.
[tt]
IF(ISNA(MATCH(D2,I:I,0)),"","DONE")
[/tt]


Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[yogia]

Hi Skip:

Did you not meant to say ...
=IF(ISNA(MATCH(D2,I:I,0)),"Done","")

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[SkipVought]

Dyslexia, like the...

dyslexic, agnostic, insomniac...

who lay awake all night, wondering...

"Is there really a Dog?"

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SkipVought]

or even this...

What's
[tt]
long

thin

green

grows in dirt

cut with a lawnmower

AND...

made from CEMENT!

[white]
GRASS!

I threw the cement in, to make it HARD!
[/white]

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[KenWright]

I think Skips was the right way round and mine was wrong:-

=IF(COUNTIF(I:I,D2),"","DONE")

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[scottian]

sorry about this. when i try any of the above formula, i get the same. So tried again with a test that i could read clearly. here was i used.

=IF(ISERROR(VLOOKUP(A2,B:B,1)),"Done","")

Old list New list result
77301 77912 Done
77311 77914 Done
77312 77916 Done
77313 77918 Done
77401 77920 Done
77961 77921
77962 77923
77963 77963
77964 77964
77965 77965
77966 77966
77967 77967
77968 77968

So this tells me that 77961 exists in the new list, but it doesnt. Also I thought the results column would indicate the old numbers which dont appear in the new file.

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[SkipVought]

My results,
=IF(ISNA(MATCH(A1,B:B,0)),"Done","")
[tt]
77301 77912 Done
77311 77914 Done
77312 77916 Done
77313 77918 Done
77401 77920 Done
77961 77921 Done
77962 77923 Done
77963 77963
77964 77964
77965 77965
77966 77966
77967 77967
77968 77968
[/tt]


Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[scottian]

No disrespect, but your using a different formula. Thank you SkipVought, It works with one column, now all I have to work out is how to get it to work with multiple column array. cheers


"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[SkipVought]

"...work with multiple column array"

???

ANY lookup only works on ONE COLUMN.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[scottian]

So it cant be done!!??

I dont believe it, Ive pestered you and youve helped me all for no reason.

So can only match one column to another column?
I need to check 4,800 records against 120,000.

Guess i need to find another way. Thanks for all your help.

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[bluedragon2]

Use an OR statement in Skip's formula to check more then one column. For example:

=IF(OR(ISNA(MATCH(A1,B:B,0),ISNA(MATCH(A1,C:C,0))),"Done","")

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Why would you have LOOKUP VALUES in multiple columns?

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[KenWright]

Methinks you need to lay out the whole problem for us, because if we only get a piece of what you are after, you may well find that there was a much better solution available.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------