Comparing two columns in two different excel sheets

1
votes
  • I have 2 excel sheets (sheet1 and sheet2)
  • Sheet1 has 10 columns, sheet2 has 20 columns (no:of rows are same )
  • Sheet2 contains all the columns that sheet1 have
  • Now, compare 12th column of sheet2 with 7th column of sheet1
  • This should result in another column names 'Matched?' in any of the sheet (lets say sheet 2)
  • This 'Matched?' columns should have 500 rows with values 'Yes' if matched (or) 'No' if not matched.

Note: Kindly please start explaining from the scratch about the provided VLOOKUP() function because i don't know about it.

1
excelexcel-formula
Do you care if you have duplicates in the 7th or in the 12th column? – Wizhi
No,it doesn't have duplicates – Hari Chinta
VLOOKUP is very good if you have a table and will search for a value, when you find the value, you want tor return a value from the column to the right of your lookup value. For comparing I think there are more efficient ways to do it :) – Wizhi

1 Answers

0
votes

This little formula will do the trick for you:

=IF(COUNTIF(Sheet1!G:G; L2)=0, "No match in Sheet1", "Match in Sheet1")

Where "Sheet1" is the name of the worksheet 1

You could then modify it to (the above formula is to clearly show the logic):

=IF(COUNTIF(Sheet1!G:G; L2)=0, "No", "Yes")

Assume we have Sheet 1:

enter image description here

Sheet 2, we apply our formula in the column V. It check if the word Apple (cell L2 exist anywhere in Sheet 1, column G). We do this for every row.

enter image description here

For next row we want to check if it exist in Sheet1, we apply the formula again, the only difference is that L2 -> L3 i.e. the reference value changes since you are looking at another row.

enter image description here