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?
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.
I haven’t used the “FIND” function, but use Excel a lot - here is what I would do:
Download TextPad (if you don’t have a text editor that does good stuff)
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.
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.
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.
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.