Excel Formula To Compare Two Columns And Return A Value. We applied 5 methods here and added nice images. Learn to
We applied 5 methods here and added nice images. Learn to make complex and precise comparisons between two columns in Excel using the VLOOKUP, EXACT, and COUNTIF functions. May 12, 2024 · To lookup values with INDEX and MATCH, using multiple criteria, you can use an array formula. Apr 29, 2024 · In this article, I will show you several techniques of using Excel formula to compare two columns for matches & return a value. Nov 13, 2023 · That's what's in store for you today in the form of this tutorial where you will learn how to compare two columns in Excel. Oct 14, 2021 · This tutorial explains how to match two columns and return a third column in Excel, including examples. Regularly clean data to avoid mismatches Discover how to find overlapping data in Excel with four practical methods. 📽️ In this Microsoft Excel video tutorial I explain how to compare two columns for matches and differences. In the example shown, the formula in H8 is: =INDEX (E5:E11,MATCH (1, (H5=B5:B11)* (H6=C5:C11)* (H7=D5:D11),0)) The result is $17. Nov 18, 2025 · Return a value in Excel based on multiple criteria using INDEX MATCH, XLOOKUP, FILTER, and SUMIFS. Note the lookup_value in XLOOKUP is 1 since the logical expressions in lookup_array create an array of 1s and 0s. Jan 27, 2013 · 3 Column 1 is my source data column, Column 2 and 3 are my reference list. 2) Ctrl+G (or F5) 3) Special 4) Row differences 5) Ok 6) Highlight cell Compare Two Lists In Excel, excel compare two lists and return differences,how to compare two inventory lists in excel,excel compare two cells text,excel formula to compare two columns and return a value,how to compare one cell with entire column in excel,how to compare two How to use the LOOKUP function in Excel to search in a single row or column and find a value from the same position in a second row or column. If both cells contained Hello or both cells contained Goodbye, or if both cells contained nothing at all, the formula would evaluate to TRUE, telling you both cells hold identical values, or nothing at all. In the example shown, the formula in H8 is: =XLOOKUP(1,(B5:B15=H5)*(C5:C15=H6)*(D5:D15=H7),E5:E15) XLOOKUP returns $29. Dec 9, 2025 · Learn how to find multiple matches in Excel using dynamic array functions, multi-criteria lookups, and regex searches. Download our Excel workbook & exercise. Read more here. Aug 23, 2025 · To compare multiple columns in Excel, you can use the conditional formatting option on the home and format the setting to “duplicates” or “uniques”. The value is simple: you get a dialog‑driven interface for statistical and engineering tools that would otherwise require multi‑step formulas or manual setup. Sep 8, 2023 · This tutorial explains how to create a formula in Excel to return Yes or No if a cell value matches a value in another range. Apr 5, 2025 · Learn how to compare two columns in Excel using formulas like IF, VLOOKUP, XLOOKUP, FILTER, and more—step-by-step with examples. 8 hours ago · When I say “install the ToolPak,” I’m not talking about downloading a sketchy plugin. Whether you’re analyzing customer lists, financial records, or inventory data, Excel offers multiple powerful methods to perform column comparisons efficiently. UNIQUE is in a class of functions called dynamic arrays. Ensure both columns have consistent data types and no extra spaces by applying TRIM. 4 days ago · On the attached, I need to match both the indicator in Column A, and Team in Column B. Use Excel to get data-driven insights in our Excel for Marketers course. [1] Technical support and product information from Microsoft. Download our practice book, modify data and exercise. How to use the MATCH function in Excel to search for a specified item in a range of cells, returning the relative position of that item in the range. Oct 17, 2024 · Learn how to find the same values in two columns in Excel using formulas like IF, VLOOKUP, MATCH, and COUNTIF. 👍👍If you have found this content useful and wan To compare two columns, use IF, ISERROR and MATCH in Excel. Note: In the current version of Excel, you Learn about the UNIQUE function, which returns a list of unique values in a list or range. Dec 2, 2024 · Learn how to compare two cells in Excel and return "Yes" if they match, using simple formulas. This means that the column containing the value you look up should always be located to the left of the column containing the return value. 5. Apr 27, 2023 · See how to compare 2 columns in Excel and how to compare and match two lists with a different number of columns. The current formula almost works, but instead of returning the email address where A matched C, it returns the email address from the same row. The VLOOKUP function can be used to search for a value in the first column of a range or table, and then return a value in the same row from a specified column. It’s a Microsoft add‑in that ships with Excel and gets toggled on. Sep 13, 2023 · How to use Substring functions in Excel to extract text from cell, get a substring before or after a specific character, find cells containing part of a string (partial match), and more. You can quickly determine if there are any duplicate values or unique entries in your data by using a few simple functions. Sep 3, 2018 · You can compare data in a column and match the content to a different column. Using conditional formatting in Excel will allow you to automatically highlight any matching values across multiple columns. Apr 30, 2024 · Excel features to compare two cells and change color. Oct 28, 2025 · The formula would return FALSE because the two cells contain different values. Basic uses and advanced formula examples for case-sensitive match, Vlookup and Match, and more. Sep 15, 2025 · The SUMIFS function in Excel is great for summing the values in a column based on multiple numerical criteria. Type the value to return. Click here to know more. It allows you to split across columns or down by rows. However, when the spreadsheets are vast or multiple spreadsheets are linked, the comparison starts to get complicated. Oct 16, 2025 · Part 5 - Compare Two Columns in Excel Using VLOOKUP This is an advanced and tricky method to compare columns in Excel containing large data. For example, we have taken a column with Class Names and along with each class name, student name has been written in another column. Find matches and differences in data, and highlight rows with matching or different values. If you're looking to spot differences, find duplicates, or identify similarities, mastering Excel's compare columns functionality is key. May 11, 2023 · These tables are on different sheets, and I want to compare the code columns between the two sheets, and if they match, I want it to return the company name from Table 2 (of the second sheet). Easy examples and formulas for accurate data lookup. Dec 23, 2025 · Comparing two columns in Excel involves using tools like formulas, conditional formatting, or built-in features to evaluate data side by side. =VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) Step 1: As a first step create a new column, label it as “Result Feb 12, 2025 · Learn how to match two columns in Excel and extract related data. Mar 14, 2025 · Case-sensitive lookup Finding the closest match INDEX and XMATCH More examples The INDEX Function The INDEX function in Excel is fantastically flexible and powerful, and you'll find it in a huge number of Excel formulas, especially advanced formulas. Log in to create a case, view open cases, and check your success plan details Oct 9, 2025 · Learn how to compare two columns in Excel using VLOOKUP to find matches, differences, missing values, and cross-sheet comparisons. It is the inverse of the TEXTJOIN function. Apr 26, 2023 · 2) Ctrl+G (or F5) 3) Special 4) Row differences 5) Ok 6) Highlight cell Compare Two Lists In Excel, excel compare two lists and return differences,how to compare two inventory lists in excel,excel compare two cells text,excel formula to compare two columns and return a value,how to compare one cell with entire column in excel,how to compare two Aug 23, 2023 · The tutorial shows how to use VLOOKUP in Excel with many practical examples: Vlookup from another sheet, from a different workbook, with wildcards, exact match vs. Apr 11, 2023 · Comparing two columns for missing values in Excel means identifying the cells in one column that do not have corresponding values in another column. Sep 20, 2025 · Learn how to compare two columns in Excel and return a value using IF, MATCH, VLOOKUP, and conditional formatting for differences. 00, the price for a Medium Blue Hoodie. This is an array formula and must be entered with Control + Shift + Enter in Legacy Excel. Our step-by-step guide makes vlookup in excel easy and efficient. Jul 23, 2025 · In the world of data analysis, knowing how to compare two columns in Excel is a fundamental skill that can unveil valuable insights and ensure data accuracy. And for this, select both columns and go to the Home Tab > Conditional Formatting > Highlight Cells Rules > Duplicate Values. If you have Excel 365 or Excel 2021, simply use the XLOOKUP function to perform a two-column lookup. Jul 2, 2024 · Learn More: Microsoft Excel for Data Analysis Overview Learn to use conditional formatting to visually compare two columns in Excel. Splits text strings by using column and row delimiters. Write the logical test in the first argument. Compare two columns of data and return missing values So I know how to compare two lists using FILTER and MATCH like this =FILTER(A:A,MATCH(A:A,B:B,0)) But I actually want to return the missing values. To do this, input the range of consecutive columns in the return array, and it will return all the columns. Aug 30, 2024 · 1. Mar 20, 2023 · See how to match data in Excel using the MATCH function. Note: visit our page about the XLOOKUP function to learn more about this great new Excel function. Jun 15, 2024 · Learn how to compare in Excel by comparing columns, cells, worksheets, or workbooks. In Excel, compare two columns with VLOOKUP to find missing values and combine with ISNA and IF to show the differences. Select a cell. Check & learn. Jan 8, 2026 · Excel formulas offer a flexible way to combine data in cells, columns, or ranges. Discover how to find overlapping data in Excel with four practical methods. This is commonly used when dealing with large sets of data where there are multiple columns with related information. We'll show you how to use different methods to compare the data in the two columns, and how to find the difference between the values in the two columns. Optionally, display "No" for unmatched cells. In this example, we'll find email addresses that are in the Left list but not in the Right list: Feb 4, 2023 · If your version of Excel does not support XLOOKUP, read how to return multiple columns using VLOOKUP instead. Jun 24, 2024 · Discussed 5 quick ways to compare two excel cells and return TRUE or FALSE. Look up a customer ID and return name, email, and phone in one formula row. Learn how to compare two columns in Excel using VLOOKUP. For example, =VLOOKUP (A2, B:C, 2, FALSE) searches column B for A2’s value and returns the corresponding value from column C. Apr 18, 2024 · Step 1: Select Your Comparison Formula There are multiple formulas you can use to compare two columns in Excel, but we’ll focus on two: the EXACT function for an exact match and the IF function for a more conditional approach. Click and drag your mouse over the columns you would like to compare. Oct 9, 2025 · Learn how to compare two columns in Excel and return common values using IF, COUNTIF, FILTER, VLOOKUP, XLOOKUP, and Conditional Formatting. approximate match, and more. See how to sum values with several AND / OR conditions in Excel 365, 2021, 2019, 2016, 2013, and lower. Follow our detailed guide for easy comparison. Mar 13, 2023 · How to construct the If match formula in Excel to see if two or more cells are equal and return logical values, custom text or a value from another cell. Enhance decision-making in your worksheets efficiently. Jul 18, 2023 · Using Microsoft Excel to compare two columns is a great way to look for discrepancies or similarities between two different sets of data. Jan 9, 2026 · Consolidate data in Excel from multiple worksheets using Power Query, Copy-Paste, and Formulas. Feb 12, 2025 · Explanation In this example, the goal is to test a value in a cell to see if it contains a specific substring. Discover powerful methods beyond VLOOKUP to create detailed reports quickly and accurately. 3. You will see the use of the humble equals operator, the EXACT function, the IF function to return custom results, and a great deal of highlighting because analytically, it really is what grasps the eye first. In this Excel table, named T_Staff, column A contains employee ID numbers, column B contains employees' ages, column C contains the number of years each employee has worked for the company, and column D details the number of tasks each Jul 18, 2024 · We showed Excel formula to compare two columns and return a value. Excel contains two functions designed to check the occurrence of one text string inside another: the SEARCH function and the FIND function. In this example, we'll find email addresses that are in the Left list but not in the Right list: Apr 25, 2024 · Comparing two columns in Excel to find matches is a common task, especially when dealing with large datasets. Used '=' sign, IF, IFERROR, EXACT, VLOOKUP, COUNTIF functions. Dec 11, 2025 · Select the columns you would like to compare. Use this result and the INDEX function to return the 3rd value in the range C2:C8. Learn how to compare rows for duplicate values here. Aug 29, 2024 · Learn how to compare two columns in Excel for matches and duplicates using methods like the equals operator, IF() function, EXACT(), VLOOKUP(), and more. This tutorial briefed you on how to compare two columns in Excel. The techniques shown can be used to find/highlight matches and differences. You can display the duplicates or the unique values. Sep 9, 2025 · Learn how to compare two columns in Excel fast. . We demonstrate multiple Conditional Formatting options and the Go To Special feature. This comprehensive guide covers seven proven techniques that will save you time and improve your Nov 4, 2025 · Use the Excel VLOOKUP Formula to Compare Two Columns and quickly identify matches or missing values in your data. In Excel, there are several techniques that allow us to easily compare two columns and streamline data. Mar 22, 2023 · Excel SUMIFS function and SUMIF with multiple criteria explained with formula examples. Jan 24, 2019 · For a row-by-row comparison, you need to use an IF function for comparing two cells in a row, and then return a value indicating the result. This Tutorial Covers: How does Excel Stores Dates in the Cells? Comparing Dates in Excel Check Whether the Dates are the Same or Not Compare Dates Using IF Formula (Greater Less/Less Than) Compare Dates That Have Time Values Operators You Can Use When Comparing Dates in Excel Mar 12, 2025 · VLOOKUP formula example and explanation of how to compare two columns in Excel using the VLOOKUP function. XLOOKUP Return Consecutive Columns One advantage of the XLOOKUP Function is that it can return multiple consecutive columns at once. Please note that all the values are string (text) and no numbers involved. Now if your spreadsheet isn't built this way, then do not use VLOOKUP. In this article, I will show you different methods you can use to compare data from different columns. May 24, 2024 · 8 quick ways to compare two columns in excel and return common values. Dec 9, 2025 · Comparing two columns in Excel is a fundamental skill that helps identify differences, duplicates, and unique values between datasets. Aug 29, 2023 · In this article we will learn how to compare values of two columns to get the values from the third column. Jul 12, 2024 · 6 Suitable ways to find duplicates in two or more columns under multiple criteria in Excel. This helps you identify matches, mismatches, or duplicates effectively. Type in the formula in an adjacent column in the same row as your data, and then copy it down all the way for the rest of the rows you'd like to compare. Learn how to use function VLOOKUP in Excel to find data in a table or range by row. The TEXTSPLIT function works the same as the Text-to-Columns wizard, but in formula form. 4. The EXACT function is case-sensitive and will return TRUE if the values in both cells are identical. In this tutorial, I'll show you various ways to compare two columns in Excel. Excel consolidation tool for multiple sheets. Here is an example of a very common method of using "" to prevent a formula from calculating if a dependent cell is blank: Practice XLOOKUP, INDEX, MATCH, XMATCH, VLOOKUP with this interactive exercise. Now, we will get the students name in another column against some randomly taken class names. Unlike the first method, you can highlight values that are in duplicates in both columns. Follow these steps to compare two columns in Excel using the VLOOKUP function with the help of the following formula. What I want is to index or match the rules from Column 2 + Column 3 and compare with Column 1 and return the matched rule value to the result column within same row. Apr 10, 2025 · Learn how to compare two columns and return values from a third column in Excel using formulas like VLOOKUP, INDEX & MATCH, or the powerful Kutools for Excel. Mar 21, 2023 · A comprehensive tutorial on using date functions in Excel explains the basics and provides formula examples of DATE, TODAY, NOW, DATEVALUE and lots of other functions. Sep 2, 2022 · The complete guide to compare two columns in Excel and get matches or differences, highlight them or even pull the data. Microsoft Excel offers options to compare and match data in a single column, multiple columns, and multiple spreadsheets. For example, use the UNIQUE function to generate two separate lists with unique or distinct values from each column, then compare for differences using the Duplicate Values option within the Conditional Formatting command. Oct 15, 2014 · So, I'm looking to search column A for a value that matches C, and return the email address from column B in a new column (D). You can use the following methods to compare data in two Microsoft Excel worksheet columns and find duplicate entries. In this video, we will learn how to Highlight the Max value row-wise by comparing two columns in Microsoft Excel⏱ Time Stamp00:00 Intro00:43 Constructing the Jul 6, 2024 · 4 suitable ways to compare two columns using the COUNTIF function. To match two columns in Excel and return a value, use the VLOOKUP or INDEX-MATCH functions. Mar 13, 2023 · See how to use VLOOKUP to compare 2 columns in Excel to return common values (matches) and find missing data (differences). Sort of like this =FILTER(A:A,MATCH(A:A,<>B:B,0)) Of course that doesn't work. I then need to return the value matching both the Year in Row 1, and Period in Row 2. Write the IF function. Use IFERROR to handle unmatched cases gracefully. Apr 25, 2024 · You can compare two columns in Excel for match by Conditional formatting, Go to Command or by using Functions such as IF, MATCH and So on. To summarize numeric values, you can use SUM, AVERAGE, MAX, and MIN or their conditional counterparts such as SUMIF. 2. This formula says IF (D3 is nothing, then return "Blank", otherwise "Not Blank"). Read below for details There are certain limitations with using VLOOKUP—the VLOOKUP function can only look up a value from left to right. This post provides 3 different ways to accomplish this so that you can use the one that is most appropriate for your data and workbook. For example, cells with "entered values" (non-formula cells) are formatted with a green fill color in the side-by-side grid, and with a green font in the pane results list. Jul 10, 2025 · Learn how to find duplicate or unique values between two Excel columnsfor accurate data comparison. To compare two columns: STEP 1: Prepare your data. 00, the price of a Large Red T-shirt. Different Excel formulas can be used to compare two columns, find the matches in the column and return a value. VLOOKUP is a staple in Excel for comparing two columns, but there are ways to enhance its functionality for more complex tasks. Mar 22, 2023 · Advanced Excel IF formula examples: multiple AND/OR criteria, nested IF statements, array formulas and more. Learn quick visual checks or automate the process for accurate data comparison. Formula examples to extract all matching values from one or several columns. But what does INDEX actually do? In a nutshell, INDEX retrieves the value at a given location Aug 13, 2024 · The best way to use XLOOKUP with multiple criteria is to use Boolean logic to apply conditions. Apr 30, 2024 · Learn how to compare two cells text in Excel using different functions and formulas with 9 different relevant examples. 8 methods with formulas & examples for Excel 2025, 365 & 2019. Learn to find matching values between two columns using the IF function.