Vlookup on concatenated field.

  • Thread starter Thread starter Shams
  • Start date Start date
S

Shams

Folks,
I am trying to do vlookup on two concatenated fields.
This is how it looks in both the files:

1692834707 1953.76 - in Set 1 (This is a concatenated
field)

1692834707 1953.76 - in Set 2 (This is a concatenated
field)

I should be able to compare this piece of information to
that in the other file and have Excel return another field
value if its true. In my case, Excel is returning N/A
even though the cell information is the same in both
worksheets. I am not sure why this is happening. Any
thoughts are welcome. Thanks.

Shams.
 
You might try to "examine" each field:

1. Use the =LEN(A1) function to very that the entries are
the same length. (Substitute the apporpriate cell refernce
for the A1.)

2. Use the =ISTEXT(A1) and/or =ISNUMBER(A1) functions to
verify that they are the same data type.
 
Shams wrote
I am trying to do vlookup on two concatenated fields. This is
how it looks in both the files:

1692834707 1953.76 - in Set 1 (This is a concatenated field)

1692834707 1953.76 - in Set 2 (This is a concatenated field)

I should be able to compare this piece of information to that in
the other file and have Excel return another field value if its
true. In my case, Excel is returning N/A even though the cell
information is the same in both worksheets. . . .
Click to expand...

The two cells may *look* the same, but that doesn't guarantee the
contain *exactly* the same contents. If one or the other has stra
trailing spaces or one uses ASCII spaces and the other HTML nonbreakin
spaces, that'd be enough for seemingly identical cells not to equal eac
other.

Have you tried

=OneWorksheet!X99=AnotherWorksheet!BZ100

? If not, try it. If it returns TRUE, then I'd need to see your looku
formula, but if it returns FALSE (as I'd be willing to bet) you have
data cleansing exercise on your hands *or* you could use

=TRIM(SUBSTITUTE(SomeRangeRef,CHAR(160),CHAR(32)))

to make all visible spaces ASCII spaces and strip off any leading o
trailing spaces
 
Back
Top