Vlookup using concatenated column indexes


M

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?
 
Ad

Advertisements

B

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
 
M

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

---
 
D

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.
 
M

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


Bernard Liengme said:
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
 
M

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.
 
Ad

Advertisements

M

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

---
 
Ad

Advertisements

M

Max

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

---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top