# comparing data in two Excel spreadsheets **URL:** \ **Category:** Factual Questions\ **Created:** [May 16, 2013, 2:26pm UTC](https://boards.straightdope.com/t/comparing-data-in-two-excel-spreadsheets/658511 "2013-05-16T14:26:05Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![iiandyiiii](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/iiandyiiii/32/7924_2.png) [@iiandyiiii](https://boards.straightdope.com/u/iiandyiiii)\ **Post date:** [May 16, 2013, 2:26pm UTC](https://boards.straightdope.com/t/comparing-data-in-two-excel-spreadsheets/658511/1 "2013-05-16T14:26:05Z")
I have one spreadsheet (call it XLS1) with thousands of line items- the relevant data is in one column, and each box reads something like “ABC, XYZ”. I have another spreadsheet (call it XLS2) with just a few hundred line items- the relevant data is in one column- each box reading something like “ABC”. All of the “ABC” boxes in XLS2 have a corresponding field in XLS1 with an associated number “XYZ” in the same box. I want to put the “XYZ” numbers from XLS1 into a column in XLS2 next to the “ABC” column in XLS2. So far, I’ve been doing “Find” in XLS1 (searching by “ABC”), and manually typing the associated “XYZ” that I find in XLS1 into XLS2. Is there an easier way? Thanks for any help. ---
**Author:** ![zut](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zut/32/2875_2.png) [@zut](https://boards.straightdope.com/u/zut)\ **Post date:** [May 16, 2013, 2:32pm UTC](https://boards.straightdope.com/t/comparing-data-in-two-excel-spreadsheets/658511/2 "2013-05-16T14:32:17Z")
I think you want VLOOKUP, which searches for a specific value in a certain column, then looks across that row to return a value from another column. (I assume that works between spreadsheets.) Excel Help should give you more information. ---
**Author:** ![DataX](https://avatars.discourse-cdn.com/v4/letter/d/e99b99/32.png) [@DataX](https://boards.straightdope.com/u/DataX)\ **Post date:** [May 16, 2013, 2:35pm UTC](https://boards.straightdope.com/t/comparing-data-in-two-excel-spreadsheets/658511/3 "2013-05-16T14:35:09Z")
I haven’t used the “FIND” function, but use Excel a lot - here is what I would do: 1. Download TextPad (if you don’t have a text editor that does good stuff) 2. Highlight the ", " from column one in sheet one - use “replace” to replace it with the regular expression " ". You may have to enable regular expressions in the replace box. 3. You know have “ABC XYZ” the space is a tab now - and if you copy and paste it back into excel it will be two columns. This is what you want. 4. Next to the column in sheet 2 - use “VLOOKUP” to lookup the value from sheet one and place it next to the relevant column in sheet 2. Use the option “FALSE” at the end. There may be a more elegant way to do this in Excel, but this will work - and is simple - and will help you out in other projects in the future. ---
**Author:** ![Jack\_Batty](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/jack_batty/32/3201_2.png) [@Jack\_Batty](https://boards.straightdope.com/u/Jack_Batty)\ **Post date:** [May 16, 2013, 2:42pm UTC](https://boards.straightdope.com/t/comparing-data-in-two-excel-spreadsheets/658511/4 "2013-05-16T14:42:32Z")
I agree with zut’s explanation – vlookup does work across separate files, but when you build the formula it will anchor all your cells; this is a good thing when defining your range, but you’ll want to un-anchor your lookup cell. Smack that F1 button and start reading up on vlookup – it isn’t as complicated as it seems once you start using it. ---
**Author:** ![iiandyiiii](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/iiandyiiii/32/7924_2.png) [@iiandyiiii](https://boards.straightdope.com/u/iiandyiiii)\ **Post date:** [May 16, 2013, 2:58pm UTC](https://boards.straightdope.com/t/comparing-data-in-two-excel-spreadsheets/658511/5 "2013-05-16T14:58:24Z")
Thanks for everyone’s help- I’m using the “VLOOKUP” along with “Text-to-column” to split the data at the commas, and it seems to be working. ---
**Author:** ![DataX](https://avatars.discourse-cdn.com/v4/letter/d/e99b99/32.png) [@DataX](https://boards.straightdope.com/u/DataX)\ **Post date:** [May 16, 2013, 3:08pm UTC](https://boards.straightdope.com/t/comparing-data-in-two-excel-spreadsheets/658511/6 "2013-05-16T15:08:30Z")
> [@iiandyiiii](#): > > Thanks for everyone’s help- I’m using the “VLOOKUP” along with “Text-to-column” to split the data at the commas, and it seems to be working. Crap - forgot about text to column :smack: