Matching Two Columns

**tjnelso** · 10-26-2012, 10:24 PM

Hello, I have two tables that contain data relating to people and I'd like to join those tables together. The name of each person is one of the columns contained in each table, so I'd like to use a VLOOKUP to match the rows appropriately. However, I'm running into a weird problem where even when I know there are rows in each table that match (i.e. they are for the same person) the lookup is not working. I don't know what's going on because, for example, I have the name "Jerry" in two cells, say C3 and F3, and when I do the following function:

=if(C3<>F3,"ERROR","Match")

the function returns "Error" even though I can see plainly that the cells are identical. Can anyone help me?

**abousetta** · 10-26-2012, 10:31 PM

Hello and welcome to the forum,

Maybe try this:

Formula:

Please Login or Register  to view this content.

abousetta

**tjnelso** · 10-26-2012, 10:50 PM

Abousetta,

Thanks for the reply, I tried the updated formula to no avail. I checked the formatting on each cell too and they are the same... I'm a little baffled.

**Moo the Dog** · 10-26-2012, 10:57 PM

Can you upload your file?

**tjnelso** · 10-26-2012, 11:21 PM

Here is the file, which contains data on baseball player statistics from this season. If you scroll through the file you'll see that there is data for each team and for each team, if you scroll to the right, there are three tables worth of data sitting next to each other. For example, cell F3 has the name 'Jonathan Albaledejo', then all the columns to the right of that are the corresponding data from one table until you get to cell AF3, which is once again 'Jonathan Albaledejo.' This is the start of a second table of data, which goes until cell BF2. Cell BF3 is once again 'Jonathan Albaledejo', and starts the third table.

My goal is to combine all the data from these tables together. The only sticky point is that the player data isn't always in the same row across tables, as it is in the first row, so I think a VLOOKUP will be necessary unless there is a better way to get to my desired result.

Thanks again for the help!

**abousetta** · 10-26-2012, 11:50 PM

Yes, there is something wrong here and I can't put my finger on it either. I created a new sheet using the Clean function since you didn't have many formulas. It seems that you copy/pasted from somewhere else and it brought along invisible characters. Maybe try it now.

abousetta

**Moo the Dog** · 10-27-2012, 12:26 AM

Cell F3 is returning CHAR(32) for the space between the first and last name. Cell BF3 is returning CHAR(160) for the space between first and last name. This is generally caused by imported data from other (non-excel) sources (web, pdf, etc.).

Here's how to get them to "match"

In cell CF3 enter this formula and fill down to CF1438: =IF(ISBLANK(BF3),"",TRIM(SUBSTITUTE(BF3,CHAR(160)," ")))
Then select CF3:CF1438 and copy
Then select cell BF3 and Paste Special - Values

Now testing with =IF(F3<>BF3,"ERROR","Match") should return Match.

Then your lookups should work.

- Moo

**vlady** · 10-27-2012, 01:03 AM

another option based on the char(160) which Moo have found

Using Find and replace

type =CHAR(160) in any cell then press F9 do not hit enter.Select the value Ctrl+C(which is actually a non-breaking space) . press escape then select the entire column (BF)open find and replace -> paste the copied character ->then go to replace with box -> hit the space bar.

try now the given formula to you or your formula