To search in another worksheet or workbook with formulas, you have to use external reference. In a small dataset, you can try spotting the differences manually by viewing two sheets side by side. In real life, the columns you need to compare are not always on the same sheet. Depending on your particular task, it can be modified as shown in further examples.Ĭompare two columns in different Excel sheets using VLOOKUP That is the basic VLOOKUP formula to compare two columns in Excel. You can also return your custom text such as "Not in List 2", "Not present", or "Not available". Our improved formula returns an empty string ("") instead of #N/A. To replace errors with blank cells, use VLOOKUP in combination with the IFNA or IFERROR function in this way: However, it delivers a bunch of #N/A errors, which may confuse inexperienced users making them think that something is wrong with the formula. The VLOOKUP formula discussed above perfectly fulfills its main objective - returns common values and identifies missing data points. For the remaining participants, a #N/A error appears indicating that their names are not available in List 2. Please notice that table_array is locked with absolute references ($C$2:$C$9) so that it remains constant when you copy the formula to the below cells.Īs you can see, the names of the qualified athletes show up in column E. The formula goes to cell E2, and then you drag it down through as many cells as there are items in List 1. You want to compare these 2 lists to determine which participants from Group A made their way to the main event. Suppose you have the names of participants in column A (List 1) and the names of those who have passed through the qualification rounds in column B (List 2). For range_lookup (4th argument), set FALSE - exact match.For col_index_num (3rd argument), use 1 as there is just one column in the array. For table_array (2nd argument), supply the entire List 2.For lookup_value (1st argument), use the topmost cell from List 1.To build a VLOOKUP formula in its basic form, this is what you need to do: When you have two columns of data and want to find out which data points from one list exist in the other list, you can use the VLOOKUP function to compare the lists for common values. How to compare two columns in Excel using VLOOKUP
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |