In this tutorial, we will create a shrinking drop-down list using the dynamic array formula engine and the FILTER function.
This is an awesome technique. Every time an item is chosen in the list, the list reduces and previously selected items are not shown.
In this example, we have a table of workers and a table of chores.
We will create a drop-down list of workers names to assign to the chores. Each time a name is assigned the list will reduce to show only the remaining names.
In this post, we will look at how you can compare two lists in Excel to highlight matched or unmatched items.
We will first identify the items that appear in both lists, and then look at how to highlight the items that appear in the first list but are missing from the second list.
The MATCH function will be used to compare both lists and will return if a record is found, or is missing. The MATCH function returns the relative position of an item in a list. If it cannot find the item, it will return the #N/A error message (learn more about the MATCH function).
Because we are applying the Conditional Formatting to the entire row, the dollar signs are important to fix different elements of the cell references.
Instead of highlighting the duplicate items when we compare two lists, this function can be altered slightly to highlight the items that are unique to the first list.
Follow the steps as before but use the formula below for the Conditional Formatting rule.
The ISNA function is used to return true if the #N/A error message is returned. Because #N/A is returned if the Match function cannot find a record, this formula identifies the missing items in a list.
This tutorial showed how the MATCH function could be used to compare two lists and highlight matched or matching items.
Depending on the end goal, Excel offers a few methods to compare lists. Using Merge Queries in Power Query is a great alternative. The FILTER function with COUNTIFS is another.
A common requirement in Excel is the need to compare two lists. In this tutorial, we will compare lists with VLOOKUP.
You might need to compare two lists to highlight missing records, highlight matching records or to return a value.
The VLOOKUP function can be used to compare lists in Excel by looking for a value from one list in another. You can then take the required action.
This tutorial shows how to compare lists with VLOOKUP. The VLOOKUP function can be used with other formulas, or Excel features, to accomplish different results involved with comparing two lists.
Let’s say we have two customer lists like the image below. Both lists are identical with the exception that list 1 has fewer customers than list 2.
We want to compare the two lists and identify the differences. The ID field will be used to check both lists as it is a unique field.
In this first example, we will look for the customers from list 1 in list 2 and return the text “Not found” if they are missing. If they are found in list 2 we will keep the cell blank.
The formula below uses the VLOOKUP function along with the IF and ISNA functions to achieve this objective.
Let’s have a look at the role each function is playing in this formula.
VLOOKUP: The VLOOKUP function has been used to look for the customer in the second list and return the customer ID (specified as 1 in the formula).
If the VLOOKUP function cannot find the customer it returns the #N/A error message. This is what we are interested in as it means the customer is on list 1, but not on list 2.
ISNA: The ISNA function is used to detect the #N/A message brought back by VLOOKUP and translate it to True. If an ID is returned it returns False.
IF: The IF function will display the text “Not found” if ISNA returns True and will keep the cell blank if ISNA returns False. This function is used to take whatever action you want based on the findings from VLOOKUP.
This formula can also be used with Conditional Formatting to highlight the differences between the two lists, instead of returning a value.
This example shows how to change the cell colour of the whole row of the table when a customer is missing from the second list. Formatting the entire row rather than just a single cell will make it clearer to identify the missing records, especially in a table with many columns.
To compare the two lists and highlight the matching records, the NOT function will need to be added to the previous formula.
=NOT(ISNA(VLOOKUP($A2,'Customers 2'!$A$2:$F$89,1,FALSE)))
The NOT function is used to reverse the result of the VLOOKUP. So instead of highlighting the missing records it highlights the matches.
There are many ways to compare lists in Excel. Formulas are great! Quick, versatile and keeping it on the grid. Merge Queries in Power Query is a fantastic alternative especially if the lists are coming from an external source.
You can use a lookup formula to return the address of a cell instead of a value within an Excel spreadsheet. You would usually be doing this to feed another function with the cell address.
In this example, we will look for a Customer using its ID and return the address of the cell that contains the customer’s country.
The Excel ADDRESS function will be used to return the address of the cell when the value is found. The function is written as follows;
=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
Row_num: The row number of the cell.
Column_num: The column number of the cell.
Abs_num: The type of reference you want to return. By default, the ADDRESS function will return an absolute reference such as $F$12. The other options are to return an absolute row/relative column, relative row/absolute column or a relative reference. You make your choice by entering a number between 1 and 4.
a1: This is the reference style. Enter 0 for an R1C1 style such as R12C6 (this is cell F6), or enter 1 for an A1 style which is the classic F6. The A1 style is the default.
Sheet_text: The name of a worksheet to use in the reference.
The Excel ADDRESS function is combined with the MATCH function to find the cell address of a value.
=ADDRESS(MATCH($I$4,$A$1:$A$92,0),6)
The MATCH function is used to look for the row number of the customer. The column number is entered as 6. Another MATCH function could have been used to locate the column number also.
No other arguments have been used meaning the answer will be returned using the defaults of an absolute cell reference, in A1 style and with no sheet text.
The example below shows a 4 being added to the ADDRESS function to return the cell address as a relative cell reference.
=ADDRESS(MATCH($I$4,$A$1:$A$92,0),6,4)
Excel lookup functions such as VLOOKUP and MATCH are not case sensitive. They will not worry about matching the case of the lookup value and the cell entries when they search.
But what if you need the cases to match? Well let’s have a look.
To ensure we have an exact match we can use the EXACT function. This function compares two strings of text to see if they are the same, and if they are it returns TRUE, but if they are different it will return FALSE.
The EXACT function looks like this;
=EXACT(text1, text2)
Text1 and Text2 represent the two text strings that you want to compare. In our example these will be the lookup value, and the range of cells we are looking in.
Because we are going to be asking the EXACT function to test one piece of text against a range of text entries, our lookup formula will be entered as an array formula. This special type of formula is entered by pressing Ctrl + Shift + Enter, instead of just Enter and are sometimes referred to as CSE formulas.
The INDEX and MATCH functions will be used for the lookup in this example. These two functions offer a very versatile Excel lookup formula. If you use functions like VLOOKUP on your spreadsheets and are not familiar with these, I suggest checking them out. You will not be disappointed (Learn more about INDEX and MATCH)
In the example below, we want to return the city that a customer is based in from a list using the customer ID. We want to force the customer ID to be entered using the correct case.
The Excel lookup formula below is used to create a case sensitive lookup for an ID entered in cell H4.
You do not type the curly braces when writing the formula. Excel will input these when you press Ctrl + Shift+Enter.
The MATCH function is looking for the value of TRUE. This is because the EXACT function has been used to test the customer ID against all the ID’s in column A. The value TRUE is returned if an exact match is found.
