Revision history [back]

You can use a combination of VLOOKUP and IF functions in Microsoft Excel to achieve this.

1. Open both worksheets that you want to compare.

2. Determine which columns contain the values you want to match. Let's say you want to match values in Column A of Sheet1 with values in Column B of Sheet2.

3. In a new column in Sheet1 (let's say Column C), use the VLOOKUP function to check if each value in Column A matches any value in Column B of Sheet 2. The formula in cell C2 would be:

=IFERROR(VLOOKUP(A2,Sheet2!B:B,1,FALSE),"Not Found")

This formula will look up the value in cell A2 of Sheet1 in Column B of Sheet2. If it finds a match, it will return the corresponding value in the first column of Sheet2 (which is also Column B, hence the 1 in the formula).

If it doesn't find a match, it will return the text "Not Found". You can change this to any other message you like.

1. Drag the formula down in column C for all the other rows you want to check.

2. Now you have a column that tells you whether each value in Sheet1 matches a value in Sheet2. To insert the corresponding values from Sheet2 into Sheet1, you can use another VLOOKUP function.

In a new column in Sheet1 (let's say Column D), use the formula:

=IF(C2<>"Not Found",VLOOKUP(A2,Sheet2!B:C,2,FALSE),"")

This formula will check if the value in Column C is not "Not Found". If it is, then it will use the VLOOKUP function to find the corresponding value in Column C of Sheet2 (which is the second column in the lookup range).

If there is no match, it will return an empty cell instead.

1. Drag the formula down in column D for all the other rows you want to insert the corresponding values into.

Now you should have the corresponding values from Sheet2 inserted into Sheet1 for all the values that matched.