VLOOKUP help (possible format issue)

  • Thread starter Thread starter Mark D
  • Start date Start date
M

Mark D

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
 
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")
 
It describes that either the A column of 'Current running April 2010' data is
having Preceding or trailing spaces or the A23 value will be having extra
spaces.

If A23 consist Preceding and Trailing Spaces then use the below formula:-
=VLOOKUP(TRIM(A23),'Current running April 2010'!$A$1:$Q$620,1,FALSE)

If A column of 'Current running April 2010' data consist Preceding and
Trailing Spaces then use the below formula:-

=VLOOKUP(A23,TRIM('Current running April 2010'!$A$1:$Q$620),1,FALSE)
Copy and paste the above formula and place the cursor in formula cell and
press F2 and press Cntrl+Shif+Enter, since it is an array formula. The
general enter will not do the trick.

Remember to Click Yes, if this post helps!
 
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
 
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 "
 
Hello again

Your formula of If A23 consist Preceding and Trailing Spaces then use the
below formula:-
=VLOOKUP(TRIM(A23),'Current running April 2010'!$A$1:$Q$620,1,FALSE)

is working very well, could you possibly help me with one more thing.

If the cells match it returns the name, excellent. If not it returns N/A,

Is there any way I can wrap around your formula that if it is N/A then ""
(would prefer to have the cell blank)

Thanks again
 
Back
Top