You are here:

Excel/Compare 2 Columns With Each Row


I have two columns A and B,
I want to match the 2 columns data of each row using only one function. At the same time I want to retrieve the Matched Data from Column B. . .!
I have used the below function to compare the data
=IF(B2=C2,C2,"")&IF(B3=C3,C3,"")&", "&IF(B4=C4,C4,"")
Please suggest the any better one. . .


I don't know how your data is arranged, but the formula you described is comparing only one column one row with the same row in the next column, so if B2=C2 and B3=C3 then the result where the formula is will be C2B3  or if cell c2 contains X and cell B3 contains Y, then the result will be XY.  I'm thinking that is not the result you want so using the & symbol to combine the results isn't a good solution.

I believe you can do what you want with one formula--If your formula is simply
That formula can be dragged or copied down and will change as appropriate as you copy down to each row.

There may also be the possibility of using the VLOOKUP formula to do what you want That formula will look thru all of the data within a column and return the matching data from column C.  For example
(Since column C and column B match then it doesn't matter which is returned by the formula.)

This formula will look in the range of C1 thru C100 and return the value from column C  It will return the first value from column C that matches the value in cell B1.

This formula can be dragged or copied down to the subsequent rows and will change as appropriate to the row.

Again, not knowing exactly how your data is arranged it is a bit difficult to give you the details of the best answer. But maybe one of these two will work for you.

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Richard Roberts


Can assist you in most areas of Excel, have been working with it for about 15 years in many types of applications, but primarily in financial and accounting applications. I am a CPA and many client or client problems have necessitated the use of excel. I am not an expert in charting, macros, or pivot tables.


Have been working with Excel for about 20 years primarily in accounting and financial areas.

BA, Certified Public Accountant

©2016 All rights reserved.