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 » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Concatenation



 
 
Thread Tools Display Modes
  #1  
Old May 16th, 2009, 04:24 AM posted to microsoft.public.excel.misc
M Thompson
external usenet poster
 
Posts: 38
Default Concatenation

Hi
I need to join the numbers from 2 cells so that I can use them in a vlookup.
When I do a concatenation, then a paste special, values, the vlookup can't
use it. Any ideas?

Thanks and have...
--
OneFineDay
  #2  
Old May 16th, 2009, 04:34 AM posted to microsoft.public.excel.misc
L. Howard Kittle
external usenet poster
 
Posts: 516
Default Concatenation

Hummm, short on info but perhaps use INDERECT on the concatenation cell in
your VLOOKUP...

VLOOKUP(INDERICT(A1),B1:C20,2,0)

where A1 has a formula something like this...

=D1&E1

HTH
Regards,
Howard

"M Thompson" wrote in message
...
Hi
I need to join the numbers from 2 cells so that I can use them in a
vlookup.
When I do a concatenation, then a paste special, values, the vlookup can't
use it. Any ideas?

Thanks and have...
--
OneFineDay



  #3  
Old May 16th, 2009, 05:29 AM posted to microsoft.public.excel.misc
Max
external usenet poster
 
Posts: 8,574
Default Concatenation

Instead of using: =VLOOKUP(A1&B1,...,2,0)
try it as: =VLOOKUP((A1&B1)+0,...,2,0)
which coerces the concatenation lookup value to a real number
It should work ok now. Success? Click YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---
"M Thompson" wrote:
I need to join the numbers from 2 cells so that I can use them in a vlookup.
When I do a concatenation, then a paste special, values, the vlookup can't
use it. Any ideas?

  #4  
Old May 16th, 2009, 05:37 AM posted to microsoft.public.excel.misc
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default Concatenation

You can try the concatenation within the formula itself
VLOOKUP(A1&B1,...,)
--
If this post helps click Yes
---------------
Jacob Skaria


"M Thompson" wrote:

Hi
I need to join the numbers from 2 cells so that I can use them in a vlookup.
When I do a concatenation, then a paste special, values, the vlookup can't
use it. Any ideas?

Thanks and have...
--
OneFineDay

  #5  
Old May 16th, 2009, 08:21 AM posted to microsoft.public.excel.misc
M Thompson
external usenet poster
 
Posts: 38
Default Concatenation

Max...this worked fantastic! Thanks much. Can you give me some idea how
this forces it to a number?
--
OneFineDay


"Max" wrote:

Instead of using: =VLOOKUP(A1&B1,...,2,0)
try it as: =VLOOKUP((A1&B1)+0,...,2,0)
which coerces the concatenation lookup value to a real number
It should work ok now. Success? Click YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---
"M Thompson" wrote:
I need to join the numbers from 2 cells so that I can use them in a vlookup.
When I do a concatenation, then a paste special, values, the vlookup can't
use it. Any ideas?

  #6  
Old May 17th, 2009, 11:16 AM posted to microsoft.public.excel.misc
Max
external usenet poster
 
Posts: 8,574
Default Concatenation

"M Thompson" wrote:
Max...this worked fantastic! Thanks much.

No prob. Glad that did it for you

Can you give me some idea how this forces it to a number?

Not exactly sure how, but that's one of the techniques to force text nums to
real nums. It "triggers" Excel to do it. You could also do a "multiply by 1"
operation instead of an "add zero" calc to achieve the same effect.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---


 




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 06:42 AM.


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