# Match string values and transfer data

1. ## Match string values and transfer data

Hi friends:

Could someone devise a macro to do the following operation? There are 2 workbooks, WorkbookA and WorkbookB. WorkbookA is a fixed reference workbook having 125 rows and columns A: P. WorkbookB can be any workbook having 50 to 300 rows and columns A :D. I want to compare string values in Column B of WorkbookA with string values in A column of WorkbookB and if they match, transfer corresponding data from WorkbookA Columns H, I, L, and P to WorkbookB columns E, F, G, and H respectively. I work in Excel 2003.

Both workbooks have a title row and data from second row onwards.  Register To Reply

2. ## Re: Match string values and transfer data

Use a formula - in cells E2:H2 of Workbook B, use these four formulas, then copy down - change the workbook names and sheet names as appropriate.

=VLOOKUP(A2,'[Workbook A.xlsm]Sheet Name'!\$B:\$P,7,FALSE)
=VLOOKUP(A2,'[Workbook A.xlsm]Sheet Name'!\$B:\$P,8,FALSE)
=VLOOKUP(A2,'[Workbook A.xlsm]Sheet Name'!\$B:\$P,11,FALSE)
=VLOOKUP(A2,'[Workbook A.xlsm]Sheet Name'!\$B:\$P,15,FALSE)  Register To Reply

3. ## Re: Match string values and transfer data

Hi Bernie Deitrick:

Thanks for your formula. It took me almost 3 hours to decipher and understand the formula and I was finally able to tweak the same to my changing requirements. Thank you VERY MUCH for suggesting this wonderful formula.  Register To Reply

4. ## Re: Match string values and transfer data

Sorry you spent so much time. I would have explained the steps to quickly create the formulas if you had asked- but I hope you learned a bit more from the exercise  Register To Reply

5. ## Re: Match string values and transfer data

Hi Bernie Deitrick:
Since you have really encouraged me with your reply, I have a few querries about this formula. Firstly, can we use this formula to highlight either a matching value or a nonmatching value? Secondly, supposing my main workbook, say Workbook 1, has columns A to H and I wish to compare column D values with column B value in Workbook 2, and on match, copy column B value of Workbook 1 to column J of Workbook 2. can we do this?. Here, we are trying to import cell values PRIOR to the reference column (column D) of the main workbook. Kindly explain.  Register To Reply

6. ## Re: Match string values and transfer data

I will start with your second question first - you need to use an INDEX(MATCH) combination. MATCH returns the position of the matching value within the range being searched:

=MATCH(Value, Range, False)

like

=MATCH(A2,'[Workbook A.xlsm]Sheet Name'!\$D:\$D,FALSE)

The False forces an exact match, which is what you want. (this formula returns an error if the value is not found, which you can use to your advantage - more later)

Then, to return the value from the previous column, use INDEX

=INDEX(Range2, MATCH(Value, Range, False))

Like

=INDEX('[Workbook A.xlsm]Sheet Name'!\$B:\$B, MATCH(A2,'[Workbook A.xlsm]Sheet Name'!\$D:\$D,FALSE))

To build up these formulas, have both workbooks open. Then in the cell where you want the formula, type

=INDEX(

then switch to the other workbook, and select the entire column of values, and make sure that you have then \$B:\$B form of the address by pressing F4 until it appears the way you want. Do the same for the MATCH part.

For highlighting values that are there, use Conditional Formatting with the Formula is option, with a formula like

=NOT(ISERROR(MATCH(D2,'[Workbook A.xlsm]Sheet Name'!\$D:\$D,FALSE)))

For CF to work,your formula need to return TRUE or FALSE for the cell being formatted, so you can create the formula in a cell to get it working and then copy it to use for the CF. To highlight values that are not there, simply apply formatting to those cells with the CF - if the CF is applied, the formatting is hidden. IF you have other options, you could also use this to show missing values:

=ISERROR(MATCH(D2,'[Workbook A.xlsm]Sheet Name'!\$D:\$D,FALSE))  Register To Reply

7. ## Re: Match string values and transfer data

Hi Bernie:

Thank you for your prompt response. It will take quite some time for me to understand what you have explained with so much details. I will come back to you in due course with my observations. Thanks once again.  Register To Reply