Excel Vlookup help please

R

robisc

I have 2 columns of data, they are in A and B, the data format exampl
is :

RA1089163

Column B has some of the same numbers as column A but fewer, I want t
know what numbers from column B match column A

I have used this forumla before:


=IF(ISNA(VLOOKUP(A2,b:b,1,FALSE)),"No","Yes")

This is in column C.

This formula works in a spreadsheet I have saved, but when I copy th
"real" data from another spreadsheet into this one that works I get th
resluts of "NO" even though I know there is some matching data, now if
fill the cells manually with the same data that is there already i
works, anyone have any ideas? Also it doesn't work if I copy th
Vlookup into the spreadsheet with the correct data, I'm wondering i
this is some sort of foramatting issue? Any help is appreciated
 
D

Dave Peterson

I don't think it's a formatting problem. But it could be a difference in
values.

Maybe one version has leading/trailing/embedded spaces in it.

If you find one you KNOW works--but doesn't, can you retype them and see if that
helps.

If you find that it does, maybe you could use David McRitchie's TrimAll()
function:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Also,

=isnumber(match(a2,b:b,0))

will return True/Falses

So these will also work for you:

=if(isnumber(match(a2,b:b,0)),"yes","no")
or
=if(iserror(match(a2,b:b,0)),"no","yes")

(I usually use =Vlookup() when I want to bring a different column back--not just
check to see if it's there.)
 
Top