How to Find Matching Values in Excel

Updated November 2024: Stop getting error messages and slow down your system with our optimization tool. Get it now at this link

1. Download and install the repair tool here.
2. Let it scan your computer.
3. The tool will then repair your computer.

In this tutorial I will show you how to compare two columns in Excel so that the values that appear in both columns are highlighted and you can do something with these duplicate values.

Using INDEX and MATCH instead of VLOOKUP

The use of VLOOKUP is subject to certain restrictions: the VLOOKUP function can only search for a value from left to right. This means that the column with the required value must always be to the left of the column with the return value. If your spreadsheet is not structured in this way, do not use VLOOKUP. Instead, use the combination of the INDEX and MATCH functions.

This example shows a small list where the value we want to search for, Chicago, is not in the left column. Therefore, we cannot use VLOOKUP. Instead, we will use the MATCH function to find Chicago in field B1:B11. Then INDEX uses this value as a search argument and finds the population of Chicago in the fourth column (column D). The formula used is displayed in cell A14.

Comparing two Columns and Highlighting Matches

If you want to compare two columns and highlight the corresponding data, you can use the replication functionality in conditional formatting.

Note that this is different from what we saw when we compared each row. In this case, we will not perform a row-by-row comparison.

Example: Comparing two columns and highlighting the corresponding data

Often you will get records that have matches but are not in the same row.

Note that the list in column A is larger than the list in column B. Some names also exist in both lists, but not in the same row (such as IBM, Adobe, Walmart).

If you want to highlight all the corresponding company names, you can do so using conditional formatting.

Here are the steps to do this:

• Select the complete record.
• Click on the Home tab.
• In the Styles group, click the Conditional Formatting option and click Conditional Formatting.
• Move the mouse pointer to the “Highlight Cell Rules” option.
• Click on Duplicate Values. Select the duplicate values in Conditional Formatting
• Make sure that Duplicate is selected in the Duplicate Values dialog box. i.e., duplicate in conditional format
• Specify the formatting. Specify formatting in conditional format
• Click OK.

The VLOOKUP Function

The V in VLOOKUP means vertical. This means that it can search for a specific value in a column. It can also return a value in the same row as the one it found.

If you have a subscription to Office 365 in the Monthly Channel, you can use the new XLOOKUP. If you only have a six-month subscription, you will be able to get it in July 2020.

Let’s use the same inventory data and try to find the price of something.

Where we’ve already looked for a line, enter the formula :

=VLOOKUP(J1,E2:G9,3,FALSE)

J1 refers to the cell with the corresponding value. E2:G9 is the range of values we are working with. But VLOOKUP only looks for a match in the first column of this range. The 3 refers to the third column, which is skipped from the beginning of the range.

So, if we enter a reference number (SKU) in J1, VLOOKUP finds the match and takes the value of the 3 columns in the cell above it. FALSE tells Excel what type of match we are looking for. FALSE means that it must be an exact match, while TRUE would say that it must be a close match.

https://www.udemy.com/blog/excel-diff/

Expert Tip: This repair tool scans the repositories and replaces corrupt or missing files if none of these methods have worked. It works well in most cases where the problem is due to system corruption. This tool will also optimize your system to maximize performance. It can be downloaded by Clicking Here