If you have a space only in A23 and not in the other sheet the COUNTIF() is
supposed to return 0 and so the formula should not work.

If you have the extra space only for lookup value then try trimming the
value as suggested by "Ms-Exl-Learner "

--
Jacob (MVP - Excel)


"Mark D" wrote:

Hi Jacob

The count function seems to be working ok, I can just amend my other formulas

I notcied what the issue was with the text though,

In the sheet where I am writing the VLOOKUP A23 etc all have 1 space after
the last character.

I dont suppose there is any quick way to get rid of this on all the lines??

Thanks again

"Jacob Skaria" wrote:

Check out for any spaces before or after the text....
=VLOOKUP(A23,'Current running April 2010'!$A$1:$Q$620,1,FALSE)

Try the below which will return the number of occurances of the word in the
other sheet.
=COUNTIF('Current running April 2010'!$A$1:$A$620,A23)

to return the name itself...
=IF(COUNTIF('Current running April 2010'!$A$1:$A$620,A23),A23,"Not found")

--
Jacob (MVP - Excel)


"Mark D" wrote:

Morning everyone.

I have a simple VLOOKUP calculation which is causing me problems.

=VLOOKUP(A23,'Current running April 2010'!$A$1:$Q$620,1,FALSE)

A23 = persons name
Column 1 in the other tab is also a persons name. I just want it to return
the name in the cell.

I keep getting N/A appearing. But if I take the ''current running april''
tab and take the name from there are copy it into A23 it works. So I assume
it is something to do with the format of both cells.

I set both of them to TEXT, but that doesnt seem to be working

Any ideas??

Thanks