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 complexity of the data and the results desired, 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 come up with custom formulas to achieve the result desired.
There are several ideas to help you find duplicates that work differently.
1. Use conditional formatting – Select the columns in question, click on “Conditional Formatting>Highlight Cell Rules>Duplicates
2. Use conditional formatting with a formula – if the inbuilt formulas for conditional formatting aren’t sufficient, the COUTIF 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, and MATCH, as well as IF functions in various forms.
1. VLOOKUP – 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 where you’re searching. For example, an index of “3” will have the formula search 3 columns to the right. The lookup value TRUE finds partial matches, while FALSE finds exact matches and returns #NIL if none is found.
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.
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 as on a single column such as sort, filter, find medians, average, and, of course, COUNTIF.
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. IFS(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.