CODEWITHSUNDEEP
excel
Keith Kaplan
Keith Kaplan

Reputation: 109

Compare data in two columns and then copy range of cells

I have two spreadsheets in excel.

In spreadsheet 1 I have texts in cells from A1 to A200. In spreadsheet 2 I have texts in cells from A1 to A200.

I want to compare all the cells in spreadsheet 1 column A to all the cells in spreadsheet 2 column A. If there is a match between the data I want the cell to output the row the match is on and/or copy a range of cells in spreadsheet 2 B2:F2 to spreadsheet 1.

I have come upon a few formulas including match(), index(), but can't seem to find the complete way to accomplish this.

Any help?

Upvotes: 0

Views: 5585

Answers (1)

ASmith
ASmith

Reputation: 126

If you want to get data values from rows with the same index value as you have in spreadsheet 1, you can use the VLOOKUP function to accomplish.

VLOOKUP(A1,'C:\PathToWorkBook\[Workbookname.xlsx]SheetName!$A$1:$F$200,2,FALSE)

This would return the value in column B of from SheetName in Workbookname.xlsx where the value in column A of that sheet has the same value as cell A1 of your current worksheet. If no corresponding value is found in Workbookname.xlsx, this function will return a '#N/A'. It should also be noted that if your key value in column A is not unique, this function will return the results from the FIRST match only and not tell you that multiple look up keys exist.

By modifying the 3rd parameter, you can change which column (B through F) in your target spreadsheet you are reading values from.

You can also use the VLOOKUP in VBA code if you desire using

WorksheetFunction.VLookup

to perform the same functionality. You can then write the result of the look up directly to a cell value.

Upvotes: 1

Related Questions