Vlookup using concatenated column indexes

[Matt]

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?

 

[Bernard Liengme]

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

 

[Max]

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

 

[Dave]

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.

 

[Matt]

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

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

 

[Matt]

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.

 

[Max]

=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]

Or rather, just: =TRIM(A1&A2)
since your expression doesn't concat a single space in-between