# Excel help: File matching

**URL:** <https://boards.straightdope.com/t/excel-help-file-matching/456580>\
**Category:** Factual Questions\
**Created:** [July 15, 2008, 4:37pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580 "2008-07-15T16:37:56Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\
**Post date:** [July 15, 2008, 4:37pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580/1 "2008-07-15T16:37:56Z")

</div>

Let’s say I have two Excel files. Column A is the key. Columns B, C, D, and E in File 1 have to match columns B, C, D, and E in File 2.

Is there a way to tell Excel to basically say:

```auto

If File_1_Column_A matches File_2_Column_A
   File_1_Column_B = File_2_Column_B
   File_1_Column_C = File_2_Column_C
   File_1_Column_D = File_2_Column_D
   File_1_Column_E = File_2_Column_E
End-If

```

I can do this of course in a different program, but it would be nice if Excel could do it.

If it can be done, can you post how to do it?

Thanks.

---

<div class="post-metadata">

**Author:** ![JustThinkin](https://avatars.discourse-cdn.com/v4/letter/j/f0a364/32.png) [@JustThinkin](https://boards.straightdope.com/u/JustThinkin)\
**Post date:** [July 15, 2008, 4:44pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580/2 "2008-07-15T16:44:07Z")

</div>

Try vlookup. I’m not sure if it’ll do what you want, but it seems to me it’s pretty close.

---

<div class="post-metadata">

**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\
**Post date:** [July 15, 2008, 4:46pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580/3 "2008-07-15T16:46:06Z")

</div>

[QUOTE=JustThinkin’]  
Try vlookup. I’m not sure if it’ll do what you want, but it seems to me it’s pretty close.  
[/QUOTE]

I should point out that I don’t know how to write code or whatever it is in Excel.

---

<div class="post-metadata">

**Author:** ![nivlac](https://avatars.discourse-cdn.com/v4/letter/n/3bc359/32.png) [@nivlac](https://boards.straightdope.com/u/nivlac)\
**Post date:** [July 15, 2008, 4:47pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580/4 "2008-07-15T16:47:27Z")

</div>

You can always create a third file that does the comparison row by row, column by column, and report any mismatches. Should take all of 1 minute to do. That’s the easiest way IMO.

---

<div class="post-metadata">

**Author:** ![Ximenean](https://avatars.discourse-cdn.com/v4/letter/x/aca169/32.png) [@Ximenean](https://boards.straightdope.com/u/Ximenean)\
**Post date:** [July 15, 2008, 4:53pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580/5 "2008-07-15T16:53:33Z")

</div>

Something like

if ‘\path[file1.xls]Sheet1’!A1 = ‘\path2[file2.xls]Sheet1’!A1

maybe? That’s how you refeer to cells in other spreadsheets, anyway.

---

<div class="post-metadata">

**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\
**Post date:** [July 15, 2008, 5:44pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580/6 "2008-07-15T17:44:09Z")

</div>

[QUOTE=Johnny L.A.]  
I should point out that I don’t know how to write code or whatever it is in Excel.  
[/QUOTE]

It isn’t real code. Vlookup is just a function and I agree that it will probably work. Vlookups are a very useful skill for data manipulation and comparisons. You should teach it to yourself. It should only take an hour or two and you will find uses for it all over the place. That type of thing is why I only have to work half as hard as my coworkers for five times the results.

---

<div class="post-metadata">

**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\
**Post date:** [July 15, 2008, 6:03pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580/7 "2008-07-15T18:03:17Z")

</div>

[QUOTE=JustThinkin’]  
Try vlookup. I’m not sure if it’ll do what you want, but it seems to me it’s pretty close.  
[/QUOTE]  
VLOOKUP is the way to do this. See the following two files for an example. ExcelFile1 has the base data, ExcelFile2 shows the formula to use to refer to it.

The other method would be to write VBA in Excel but that’s probably overkill for this problem.

[www.seigle.net/ExcelFile1.xls](http://www.seigle.net/ExcelFile1.xls)  
[www.seigle.net/ExcelFile2.xls](http://www.seigle.net/ExcelFile2.xls)

---

<div class="post-metadata">

**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\
**Post date:** [July 15, 2008, 6:22pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580/8 "2008-07-15T18:22:33Z")

</div>

[QUOTE=Shagnasty]  
It isn’t real code. Vlookup is just a function and I agree that it will probably work.  
[/QUOTE]

Ah. I’d never heard of it before. I opened the Help menu and am printing off the section on vlookup…

OK, I’ve printed it off and skimmed it. It doesn’t have an example of what I want to do, which is to replace stuff in several columns with the same columns from another file if the value in the first column matches.

I’m not sure where they’re putting the formulas. Somewhere in the single file they’re using?

Basically I can click buttons in Excel, and I can do formulas. I don’t know any of the Power User stuff.

> [@Usram](#):
>
> Something like
> 
> if ‘\path[file1.xls]Sheet1’!A1 = ‘\path2[file2.xls]Sheet1’!A1

Would the whole thing be something like this?

if ‘\path[file1.xls]Sheet1’!A1 = ‘\path2[file2.xls]Sheet1’!A1, ‘\path[file1.xls]Sheet1’!B1 = ‘\path2[file2.xls]Sheet1’!B1, ‘\path[file1.xls]Sheet1’!C1 = ‘\path2[file2.xls]Sheet1’!C1, ‘\path[file1.xls]Sheet1’!D1 = ‘\path2[file2.xls]Sheet1’!D1

What’s the exclamation point? Would that go into the first cell, and then be copied for the whole file? What if the sorted files don’t match? For example, File 1 has A0001, A0003, A0004 and A0005, and File 2 has A0001, A0002, A0003, A0004 and A0005. Will the B, C and D column values from File 2 replace the ones in File 1 for all of them? Or will it try to match File 1 A0003 to File 2 A0002 because of their positions in the file?

EDIT:

**CookingWithGas** : I’ll have a look at that. I clicked on Open File on the first link and it’s not doing anything. I don’t know if it locked up the browser, or if it just takes forever to download.

---

<div class="post-metadata">

**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\
**Post date:** [July 15, 2008, 6:43pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580/9 "2008-07-15T18:43:49Z")

</div>

**CookingWithGas** : It crashed my browser when I tried to open the first one. I went back and saved them both, and then tried to open them. I get a message saying the files are corrupt.

FWIW, here’s how I’m doing it outside of Excel:

```auto

*********************************************************************
 *********************************************************************
JOB INPUT(FILEA KEY PREV-AR FILEB KEY CUR-AR) FINISH(Z-REC)
*********************************************************************
 *********************************************************************
      
IF MATCHED     
   OUT-AR = PREV-AR
   OUT-NAME = PREV-NAME
   OUT-NAME2 = PREV-NAME2
   OUT-ADDR1 = PREV-ADDR1   
   OUT-ADDR2 = PREV-ADDR2
   OUT-CITY = PREV-CITY
   OUT-STATE = PREV-STATE
   OUT-ZIP = PREV-ZIP
   OUT-REST = CUR-REST
   PUT FILEC           
   COUNTER1 = COUNTER1 + 1
ELSE 
IF NOT MATCHED FILEA
   NOT-REC = CUR-REC    
   PUT NONMATCH       
   COUNTER2 = COUNTER2 + 1 
END-IF   
END-IF

```

---

<div class="post-metadata">

**Author:** ![Ximenean](https://avatars.discourse-cdn.com/v4/letter/x/aca169/32.png) [@Ximenean](https://boards.straightdope.com/u/Ximenean)\
**Post date:** [July 15, 2008, 7:05pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580/10 "2008-07-15T19:05:21Z")

</div>

I think we need some clarification of what you’re trying to do, because that seems to slightly contradict the OP. It looks to me like you’re saying there

```auto

for each row in file A
  if there's a row in file B with the same key /* i.e. same first column */
    write file A's row to file C
    increment count of matching rows
  else
    write file A's row to file D
    increment count of non-matching rows in file A

```

That is, you want a list of the rows in file A where the entry in the first column is the same as any entry in the corresponding column in file B. Right?

---

<div class="post-metadata">

**Author:** ![Nava](https://avatars.discourse-cdn.com/v4/letter/n/da6949/32.png) [@Nava](https://boards.straightdope.com/u/Nava)\
**Post date:** [July 15, 2008, 7:12pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580/11 "2008-07-15T19:12:06Z")

</div>

[QUOTE=Johnny L.A.]  
Ah. I’d never heard of it before. I opened the Help menu and am printing off the section on vlookup…

OK, I’ve printed it off and skimmed it. It doesn’t have an example of what I want to do, which is to replace stuff in several columns with the same columns from another file if the value in the first column matches.  
[/QUOTE]

Column A is in file 1.

Column B and C are in file 2.

In the column to the right of A, do a vlookup(“column A”,“columnsBandC”,2,false) only with the appropriate syntax; this will copy the value in column C (the second column in the “columnsBandC” range) to the column where you wrote the lookup whenever the value in B matches that in A. Make sure that both files are sorted by columns A and B respectively.

Once the lookup is done, copy that whole column and do a “paste special” as “values” so it doesn’t try to update itself, takes up less space, etc.

---

<div class="post-metadata">

**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\
**Post date:** [July 15, 2008, 7:17pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580/12 "2008-07-15T19:17:44Z")

</div>

[QUOTE=Usram]  
I think we need some clarification of what you’re trying to do, because that seems to slightly contradict the OP. It looks to me like you’re saying there

```auto

for each row in file A
  if there's a row in file B with the same key /* i.e. same first column */
    write file A's row to file C
    increment count of matching rows
  else
    write file A's row to file D
    increment count of non-matching rows in file A

```

That is, you want a list of the rows in file A where the entry in the first column is the same as any entry in the corresponding column in file B. Right?  
[/QUOTE]

Almost. If there’s a match, I’m taking some information from FILEA and some information from FILEB from each row; not the whole row from FILEB. In the code I posted, this is from a program that reads a text file; so REST is the rest of the file, which contains several rows.

This is what I do: Open the Excel file. Get rid of the commas. Save as Comma delimited. Open the .csv file in Access. Export to a text file. Do the same with the file I want to match to. Run Easytrieve to compare the two text files and write two output files. Open the output .txt files in Excel. Modify the non-matches and cut-and-paste them into the matched file. Sort and save as Excel.

I was hoping there would be a way of doing that without so many steps. Like just tell Excel to look at the other file and bring over selected cells when the value in the first column matches, and keep the original data when they don’t.

Apparently vlookup can do that, but I’ve never used it and Microsoft’s ‘Help’ is as unhelpful as most of their documentation.

---

<div class="post-metadata">

**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\
**Post date:** [July 15, 2008, 7:25pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580/13 "2008-07-15T19:25:02Z")

</div>

I’m sorry it caused a crash.

Here is the formula you need in B1 of the second file. $D$22 may be some higher or lower number, use whatever your last row is.

Copy to C1, and change the last 2 to a 3. Copy to D2 and change it to 4.

=VLOOKUP($A1,’[ExcelFile1.xls]Sheet1’!$A$1:$D$22,2,FALSE)

---

<div class="post-metadata">

**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\
**Post date:** [July 15, 2008, 7:31pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580/14 "2008-07-15T19:31:23Z")

</div>

[QUOTE=Nava]  
Once the lookup is done, copy that whole column and do a “paste special” as “values” so it doesn’t try to update itself, takes up less space, etc.  
[/QUOTE]

Ah. Of course I’d have to Copy and Paste special. I forgot about that.

OK I tried this, but there’s an error in it somewhere.

=vlookup(Sheet1 !A,‘H:\Input\_File\_Prev.xls’,Sheet1 !D,TRUE)

---

<div class="post-metadata">

**Author:** ![scout1222](https://avatars.discourse-cdn.com/v4/letter/s/34f0e0/32.png) [@scout1222](https://boards.straightdope.com/u/scout1222)\
**Post date:** [July 15, 2008, 7:37pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580/15 "2008-07-15T19:37:09Z")

</div>

That looks like a lot more than one error. Sheet1 !A? What is that? I think you want a range in there - like A1 through A25.

I also think there are some missing/stray single quotes in there.

---

<div class="post-metadata">

**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\
**Post date:** [July 15, 2008, 7:37pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580/16 "2008-07-15T19:37:36Z")

</div>

[QUOTE=CookingWithGas]  
I’m sorry it caused a crash.

Here is the formula you need in B1 of the second file. $D$22 may be some higher or lower number, use whatever your last row is.

Copy to C1, and change the last 2 to a 3. Copy to D2 and change it to 4.

=VLOOKUP($A1,‘[ExcelFile1.xls]Sheet1’!$A$1:$D$22,2,FALSE)  
[/QUOTE]

Not quite following what that’s doing, but I pasted it into the first cell of a test file:

=VLOOKUP($A1,[‘H:\800326\_Prev.xls’]Sheet1!$A$1:$D$22,2,FALSE)

It doesn’t like the file name.

Anyway, why FALSE? Shouldn’t it be TRUE? That is, if A1 in the current file matches any cell in Column A that would be TRUE, wouldn’t it?

---

<div class="post-metadata">

**Author:** ![scout1222](https://avatars.discourse-cdn.com/v4/letter/s/34f0e0/32.png) [@scout1222](https://boards.straightdope.com/u/scout1222)\
**Post date:** [July 15, 2008, 7:38pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580/17 "2008-07-15T19:38:28Z")

</div>

Also, have you tried the function wizard? That might help you build your lookup formula instead of trying to manually translate from our template here.

---

<div class="post-metadata">

**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\
**Post date:** [July 15, 2008, 7:38pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580/18 "2008-07-15T19:38:43Z")

</div>

[QUOTE=scout1222]  
What is that?  
[/QUOTE]

You’re asking **me?**? I have no idea what I’m doing. We don’t use Excel that way here. 😛

---

<div class="post-metadata">

**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\
**Post date:** [July 15, 2008, 7:40pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580/19 "2008-07-15T19:40:56Z")

</div>

[QUOTE=scout1222]  
Also, have you tried the function wizard? That might help you build your lookup formula instead of trying to manually translate from our template here.  
[/QUOTE]

There’s a function wizard? I just followed along with this little yellow box that popped up (like when you’re filling in a cell with the same information as in another cell and you can just click on it).

---

<div class="post-metadata">

**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\
**Post date:** [July 15, 2008, 7:43pm UTC](https://boards.straightdope.com/t/excel-help-file-matching/456580/20 "2008-07-15T19:43:16Z")

</div>

[QUOTE=Johnny L.A.]  
**CookingWithGas** : It crashed my browser when I tried to open the first one.  
[/QUOTE]  
I just realized I FTP’d in ASCII mode instead of binary. Sorry. I assume it’s a moot point now but I will re-post if you would find it helpful.

[Next page](https://boards.straightdope.com/t/excel-help-file-matching/456580.md?page=2)