Vlookup using concatenated column indexes
 

I'm using Excel 2003 and trying to write a vlookup formula in Sheet 1 to
lookup an array in Sheet 2: Column A (text), Column B (text), Column C
(number) and Column D (number). The text in Columns A and B appear in either
column but never both on the same row. Like wise for Columns C and D. The
array data is a dump from another program and for some reason cannot dump the
data into the one column. To combat this, I am trying to concatenate Columns
A and B and put the text into Column E. I am then trying to concatenate
Columns C and D and put the value into Column F. I then try to do a vlookup
using columns E and F as the array but the vlookup does not see the
concatenated text string.

I have searched the discussion group page for over an hour now and still
can't find anything. Can anyone help please?
--
Regards
Matt

Vlookup using concatenated column indexes
 

How are you concatenating? Are you making sure spaces are remove with TRIM?
I would be happy to look at sample data (private email)
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Matt" wrote in message
...
I'm using Excel 2003 and trying to write a vlookup formula in Sheet 1 to
lookup an array in Sheet 2: Column A (text), Column B (text), Column C
(number) and Column D (number). The text in Columns A and B appear in
either
column but never both on the same row. Like wise for Columns C and D.
The
array data is a dump from another program and for some reason cannot dump
the
data into the one column. To combat this, I am trying to concatenate
Columns
A and B and put the text into Column E. I am then trying to concatenate
Columns C and D and put the value into Column F. I then try to do a
vlookup
using columns E and F as the array but the vlookup does not see the
concatenated text string.

I have searched the discussion group page for over an hour now and still
can't find anything. Can anyone help please?
--
Regards
Matt

Vlookup using concatenated column indexes
 

You could also upload your sample file/data using a free filehost, then post
a link to it here.

Eg, you could use this free filehost to upload:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload,
then paste it here
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Vlookup using concatenated column indexes
 

Hi,
If your original data (columns A, B, C, D) is imported from somewhere else,
it may contain trailing or leading spaces, or character 160. The concatenated
data (Columns E, F) will include these. Use the TRIM function to get rid of
excess spaces, and the SUBSTITUTE function to get rid of character 160.
=TRIM(SUBSTITUTE(E1,CHAR(160)," ")) and copy down.
Regards - Dave.

Vlookup using concatenated column indexes
 

Thanks Bernard. The imported data in Columns A and B (text) contained a space
at the end of each line of text.

The revised formula that I put into Cell E1 on Sheet 2 was:
=concatenate((trim(A1)),(trim(A2)))

Thank you for your assistance. Worked great.
--
Regards
Matt


"Bernard Liengme" wrote:

How are you concatenating? Are you making sure spaces are remove with TRIM?
I would be happy to look at sample data (private email)
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Matt" wrote in message
...
I'm using Excel 2003 and trying to write a vlookup formula in Sheet 1 to
lookup an array in Sheet 2: Column A (text), Column B (text), Column C
(number) and Column D (number). The text in Columns A and B appear in
either
column but never both on the same row. Like wise for Columns C and D.
The
array data is a dump from another program and for some reason cannot dump
the
data into the one column. To combat this, I am trying to concatenate
Columns
A and B and put the text into Column E. I am then trying to concatenate
Columns C and D and put the value into Column F. I then try to do a
vlookup
using columns E and F as the array but the vlookup does not see the
concatenated text string.

I have searched the discussion group page for over an hour now and still
can't find anything. Can anyone help please?
--
Regards
Matt

Vlookup using concatenated column indexes
 

Thanks Dave. The imported data in Columns A and B (text) contained a space
at the end of each line of text.

The revised formula that I put into Cell E1 on Sheet 2 was:
=concatenate((trim(A1)),(trim(A2)))

Thank you for your assistance. Worked great.

--
Regards
Matt


"Dave" wrote:

Hi,
If your original data (columns A, B, C, D) is imported from somewhere else,
it may contain trailing or leading spaces, or character 160. The concatenated
data (Columns E, F) will include these. Use the TRIM function to get rid of
excess spaces, and the SUBSTITUTE function to get rid of character 160.
=TRIM(SUBSTITUTE(E1,CHAR(160)," ")) and copy down.
Regards - Dave.

Vlookup using concatenated column indexes
 

=concatenate((trim(A1)),(trim(A2)))

Think this shorter version: =TRIM(A1&" "&A2)
would work just as well for you

You can use the ampersand operator: &
to do the concatenations
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Vlookup using concatenated column indexes
 

Or rather, just: =TRIM(A1&A2)
since your expression doesn't concat a single space in-between
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---