If you need to compare cell values in three different columns, you likely realize that it is a different game altogether compared to when you're only dealing with 2 columns. The standard formulas may or may not work depending on the data's complexity and the desired results, but we will help you do just that.
Excel has many wonderful tools to help with comparison, including conditional formatting and lookup. It is also possible to develop custom formulas to achieve the desired result.
Compare columns to Identify Duplicates
There are several ideas to help you find duplicates that work differently.
You can compare three excel coluns to identify duplicates.
1.Let's use the following dummy data as an example.
2. Use conditional formatting – Select the columns you wish to compare
3.click on "Conditional Formatting>>Highlight Cell Rules>>Duplicates
4. Choose the colors for highlighting the duplicate cells by clicking on the drop-down button
5. Press okay, and all the duplicate values will be highlighted with the color you have just selected.
NOTE: if the inbuilt formulas for conditional formatting aren't sufficient, the COUNTIF function is a great alternative. Use it in the form =COUNTIF(otherlist,firstcellinselectedlist) = 0. It works by comparing cell values in two or more lists, depending on how you use it. Go to the Styles tab>Conditional Formatting>New Rule. Choose a fill color for your new formula and hit enter.
When you're looking for values that match one or more criteria, a lookup operation is what you will do. There are various functions you can use to do this, including VLOOKUP, LOOKUP, INDEX, MATCH, and IF functions in various forms.
The formula is used in this format: =VLOOKUP(criteria, range, column index, range lookup value). The criteria is a value or cell with the value you're looking to match. The range is the column(s) where you're looking for the value, the index is the column count of that column where you think the answer might be found, which is reckoned on the right of the one you're searching. For example, an index of "3" will have the formula search three columns to the right. The lookup value TRUE finds partial matches, while FALSE finds exact matches and returns #NIL if none is found.
- For example, we can check the price of any product on the 3rd column on the following set of data.
- Write the following formula to look up the price of Huawei =VLOOKUP(H3,A2:D7,3,FALSE)
H3 represents the item
A2:D7 represents the range of columns
3 represent the position of a column with price
- Press enter to populate the price
2. Index and Match
For advanced lookups and returns the position of a value in a given range. Used in the formats =MATCH (first argument, range, second argument) and INDEX (range, argument). Used together, they return values based on the search on a 2D range.
Let's use the data below for our example. We will use the Index and match function to obtain the price of Tesla from the table
Enter the following formula in a blank cell.=INDEX(C2:C7,MATCH(B13,A2:A8,0))
C2:C7 is the column containing the range
B13 contains the product that we are looking for the price
A2:A8 is the product list range
0 indicates unsorted lists
Download Index and Match practice sheet Index match practice sheet
3. Concatenate and count – Concatenating is combining the values of two or more columns together without adding them up. The formula for concatenating resembles this one: A1&A2&A3. All the values, unless they are dates or times, will appear joined together, which then allows you to do other operations on a single column such as sort, filter, find medians, average, and, of course, COUNTIF.
- Let's join the following names using concatenate() function
- Enter the following formula =CONCATENATE(C2," ",B2," ",A2) and press enter
"" The parenthesis insert space between the names
- Repeat the same for the rest of the names
Compare values with custom formulas
The most advanced and probably the best way for sophisticated analytics is to use custom formulas. These can be formed from IF functions, MATCH and NOMATCH arguments, various array formulas, among others.
Since custom formulas would be too many to list all here, we'll just have a few examples.
1. =IF(AND(A1=A2,A2=A3),"MATCH", "NOMATCH") – to see if values from three cells match. Works the same for cells in different columns.
2. IF(A2>10,1,AND(A2>25,B2>25),2) to compare values.
3. =INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$14:B14, $B$3:$B$12)+IF(((COUNTIF($D$3:$D$11, $B$3:$B$12)>0)+(COUNTIF($F$3:$F$12, $B$3:$B$12)>0))=2, 0, 1), 0)) – this is an array formula example – hold down shift and control keys, then press enter and release all keys.
Download CONCAT and MATCH NO MATCH practice sheet