You have a list of people who are a member of a club or something. You have another list of who signed up for the field trip. Are they club members? You need to know how to match names in two lists. The answer is always a spreadsheet and here is how you can easily determine if a list has matching members.
Match Names in Two Lists
If you want to MATCH names then use the MATCH formula.
=IF(ISERROR(MATCH(A2,$B$2:$B$31,0)), "No", "Yes")
search_key: The value you’re looking for. It can be a number, text, or a reference to a cell containing the value.
range: The range of cells where you want to search for the search_key.
search_type: (Optional) Specifies how the MATCH function searches for the search_key within the range:
1 or omitted: (Default) Finds the largest value that is less than or equal to the search_key. The range must be sorted in ascending order.
0: Finds the first value that is exactly equal to the search_key. The range can be in any order.
-1: Finds the smallest value that is greater than or equal to the search_key. The range must be sorted in descending order.
What is the MATCH Formula
Notice in this spreadsheet example that column B has the full roster of names. Column A has who signed up for the trip. Column C uses the MATCH formula to see if the names in column A match who is in the list in column B.
Note to MATCH they must match EXACTLY! If they typed their name and put Bob instead of Robert or did not include their last name the name will not match.
(Optional) Search Type
For the basic MATCH formula, the first element is what you want to match. In this case, it is the first name in the list in column A. Comma. Then the range of the list I am looking to match the value in.
What will this return?
Not what you expect…
For this formula on the spreadsheet the response is 30. What does that mean? It should be that it is the 30th item in the list, but it is not. If I include the optional comma zero at the end, it returns 12, as it is in the 12th row. The comma zero finds the FIRST match in the list.
But How is That Helpful to Match?
Returning a number may or may not be helpful to you in your quest to match names in two lists.
ISERROR Function
So either the name is a match or it is not. If it is not, there will be an error. I do not want to see errors on the spreadsheet, so to tackle that the ISERROR function will return true or false. True it is an error or false it is an error.
IF it is an Error Then....
Seeing “True” or “False” for an error seems a little backwards. Because IF it is true there is an error that means there is NOT a match. That certainly is confusing. Thus, a 3rd function would be helpful. Use the IF function to say IF there is an error then return “NO.” However, IF there is not an error, then return “YES.”
=IF(ISERROR(MATCH(A2,$B$2:$B$31,0)), "No", "Yes")Use BYROW Function
The formula above checks only ONE value. You have a list or range of values. You want to apply this formula for each name in the sign up list against who is eligible. BYROW might be a good option. This allows you to use a LAMBDA to custom define the formula for each row in the range.
=BYROW(A2:A10,LAMBDA(x, IFNA( MATCH(x,$B$2:$B$31,0),“NO”)))
This does work, however notice the option for “YES” is gone. The MATCH will return the position of the range for each item in the list. If the name is NOT on the list in column B, the word “NO” will let you know this person doesn’t match.
