Concatenation

  • Thread starter Thread starter M Thompson
  • Start date Start date
M

M Thompson

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...
 
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
 
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
 
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
 
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
 
Max...this worked fantastic! Thanks much. Can you give me some idea how
this forces it to a number?
 
Max...this worked fantastic! Thanks much. Can you give me some idea how
this forces it to a number?
 
M Thompson said:
Max...this worked fantastic! Thanks much.
Click to expand...
No prob. Glad that did it for you
Can you give me some idea how this forces it to a number?
Click to expand...
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
---
 
M Thompson said:
Max...this worked fantastic! Thanks much.
Click to expand...
No prob. Glad that did it for you
Can you give me some idea how this forces it to a number?
Click to expand...
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
---
 
Back
Top