A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

VLOOKUP help (possible format issue)



 
 
Thread Tools Display Modes
  #1  
Old April 30th, 2010, 10:09 AM posted to microsoft.public.excel.worksheet.functions
Mark D
external usenet poster
 
Posts: 62
Default VLOOKUP help (possible format issue)

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
  #2  
Old April 30th, 2010, 10:30 AM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default VLOOKUP help (possible format issue)

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

  #3  
Old April 30th, 2010, 10:38 AM posted to microsoft.public.excel.worksheet.functions
MS-Exl-Learner
external usenet poster
 
Posts: 135
Default VLOOKUP help (possible format issue)

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!

--------------------
(Ms-Exl-Learner)
--------------------


"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

  #4  
Old April 30th, 2010, 11:29 AM posted to microsoft.public.excel.worksheet.functions
Mark D
external usenet poster
 
Posts: 62
Default VLOOKUP help (possible format issue)

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

  #5  
Old April 30th, 2010, 12:20 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default VLOOKUP help (possible format issue)

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

  #6  
Old April 30th, 2010, 12:43 PM posted to microsoft.public.excel.worksheet.functions
Mark D
external usenet poster
 
Posts: 62
Default VLOOKUP help (possible format issue)

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

"Ms-Exl-Learner" wrote:

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!

--------------------
(Ms-Exl-Learner)
--------------------


"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

  #7  
Old April 30th, 2010, 12:50 PM posted to microsoft.public.excel.worksheet.functions
Mark D
external usenet poster
 
Posts: 62
Default VLOOKUP help (possible format issue)

Sorry Ms-Exl-Learner, I worked it out

Thanks again

"Mark D" wrote:

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

"Ms-Exl-Learner" wrote:

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!

--------------------
(Ms-Exl-Learner)
--------------------


"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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:04 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.