how to use vlookup in excel 2013 to compare two columns

In cell C2, initiate a formula using a conditional IF statement as shown below: Step 1: Consider the same example as the previous one. Let’s create a custom formula using a conditional IF statement that works in our favor. Having a proper phrase that can let you know whether the data is matching or not matching will be helpful in such cases. Sometimes, just getting values as TRUE/FALSE might not be a good representative when we are comparing two columns for matches. Now, we will see a different method where we are going to use a conditional IF statement to check whether two of the columns have matches. Let’s see a different method to check the same. This is one method using which we could check whether the values under two columns have a match or not. You can validate this by using the Len function as well. But the result is FALSE because we have an extra spacing at the end of cell B5. For Example, see the fifth row where two strings are looking exactly similar, and they should match with each other. There are sometimes extra spaces that allow the text to not match using the equality operator.Note that this criterion for matching does not consider whether the text is in the upper or lower case.Same as with the 4 th output where A4 has value as “Google” and B4 has “Googl”. This is because, for output in C2, A2 has the value “Sweets”, whereas the value in B2 is “swetes”. You can see some of the cells will match with each other from both of the columns, and some of those will not.Now, drag the formula across all the working cells and see the output as shown below: If both values don’t match, it will return Boolean output FALSE. If both values match, then it will return Boolean output TRUE. This is a logical formula that checks whether the value present in cell A2 is exactly matching with the value present in cell B2.

Step 3: Now, we wanted to check if the value present in A2 is matching with the value present in B2 for that, use =B2 as the next argument. Step 2: Since we wanted to compare the value present in cell A2, use A2 as the first argument under C2 after the equals to sign. We will discuss some methods to compare two columns for matches. Methods to Compare Two Columns for Matches We will walk you through some of the methods to achieve this. This way or that, you may need some methods to compare two columns for matches. Or, you may be having some comparison and trying to see if that matches between the two columns from two different datasets for with the same properties. If it does, you may opt to delete one of the two columns. You may want to check if the values in the two columns are matching. Sometimes, you just want to truncate your dataset, and while doing that, it is of your core interest to reduce the duplication. This has importance based on the situation. When we are doing data analysis in Excel, one of the most important tasks is comparing two columns and figuring out whether the values within them match row by row. Other functions are available for that (although yours works fine).Excel functions, formula, charts, formatting creating excel dashboard & others What is Comparing Two Columns in Excel for Matches? You are searching the first column of the range, and returning the value in that same column. is fundamentally different from : the first leaves a formula, whose result may change if the cells referenced change the second writes a fixed value.īoth solutions by L42 qualify Ranges suitably. That is why the first soluton by L42 does not need any extra error handling (it is taken care by =VLOOKUP.). This catching is actually performed by the function VLOOKUP as entered in a cell (and, if the sought-for value is not found, the result of the error is presented as #N/A in the result). I will point out a few additional notable points:Ĭatching the error as done by chris nielsen is a good practice, probably mandatory if using (although it will not suitably handle case 2 above). Ws2.Range('E2').Formula = CVErr(xlErrNA) ' Use whatever you want If the ActiveSheet is Sheet1, then using Range('D2') without qualifying it will be searching for Sheet1!D2, and it will throw the same error even if the sought-for value is present in the correct Range.Ĭode accounting for this (and items below) follows: Sub srch() The value is actually not present (pointed out by chris nielsen).

The error you are getting is likely due to the sought-for value Sheet2!D2 not being found in the searched range Sheet1!A1:A65536. Please read this explanation and its conclusion, which I guess will give essential information. The answer your question: the correct way to refer to a different sheet is by appropriately qualifying each Range you use.

How to use vlookup in excel 2013 to compare two columns