![]() ![]() Open the "Home" tab on the top bar and click on "Conditional Formatting" and "New Rule" again. Select an empty cell where you want the results of the VLOOKUP function to appear. To use the VLOOKUP function to compare two lists, follow these steps: We can use this function to compare two lists by searching one list based on the value of another. ![]() VLOOKUP is an Excel function that allows users to fetch data easily. Related: Basic Excel Formulas and How To Use Them Use the VLOOKUP method Move your mouse to the bottom-right corner of this cell until the cursor turns into a white cross.Ĭlick and drag your mouse downward to create a new list in this column that compares all the data in your other two lists. Identify whether your cell reads “Match” or “Not a match”, depending on the data in cells A1 and B1. Press the “Enter” key or select another cell to apply the formula. Use the formula “=IF(A1=B1, “Match”,”Not a match”)" to test if the cell in A1 is the same as B1, replacing the references to match your own data. Select the cell of your new third column where you want the result to appear. For example, if you have two lists with names, you can use a column next to them to see if the names match across the row. You can display the differences between rows easily by adding a third column. Select a fill color to highlight any differences for future reference. Select the “Row differences” option, then click “OK” to highlight all the cells with differences between the two rows. Press the “F5” key to open the “Go to Special” tool. To use this function, follow these steps: ![]() Comparing across rows allows you to compare cells that are next to one another, rather than searching through an entire list for each value. To quickly compare two lists across identical rows, you can use the row difference feature in Excel. Replace “B1:B10” with the range for your second list, and “A1” with the first cell reference on your first list.Ĭlick the “Format” button to change how Excel formats the cells based on your new rule, then click the “OK” button to complete your new rule. Write the formula, “=COUNTIF(B1:B10,A1)=0" in the “Edit the Rule Description” section, which checks whether the value in cell "A1" exists in range "B1:B1." Select the “Use a formula to determine which cells to format” option in the following menu, which is underneath the “Select a Rule Type” heading. Under the “Home” menu, select “Conditional Formatting”, followed by “New Rule.” To create a new formatting rule, follow these steps: This allows you to accomplish many tasks, like seeing if certain items that appear in one list also appear in the other. You can also create your own formatting rule to compare two lists. ![]() Related: How To Use Conditional Formatting in Excel in 5 Steps Create a new formatting rule Select “Unique” from the first dropdown menu, followed by your preferred formatting in the second menu.Ĭlick “OK" to view the unique values in each list highlighted. Select “Highlight Cells Rules” in this menu, followed by “Duplicate Values.” Under the “Home” menu, select “Conditional Formatting.” For example, you can highlight only the unique values across the two lists. With this method, you adjust the appearance of a cell depending on certain conditions. Related: 20 Best Excel Tips To Optimize Your Use How to compare two lists in Excelīelow are several methods you can use to compare two lists in Excel: Use conditional formattingĪn easy way to compare two lists in Excel is with conditional formatting. By comparing these two lists, you could quickly identify whether a company has the right address information and correct any spelling mistakes. The first contains addresses stored by the company's computer system, while the other contains information supplied by customers themselves. For example, a company may have a spreadsheet for customer addresses with two separate columns. There are many scenarios where this function can help you perform essential work tasks. Why compare two lists in Excel?Ĭomparing two lists in Excel allows you to determine the differences between two sets of data. In this article, we provide you with several methods for comparing two lists in Excel, along with some examples of when you might want to do this. By learning how to compare lists in Excel, you can often conduct processes like data validation much faster. One helpful feature of Excel is the ability to compare datasets and find the differences between them. Microsoft Excel is a popular tool for data organization and analysis. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |