How to Check if a Value Exists in Another List in Excel
Question details
The user needs a method to determine whether items from one list are present in a second list using Excel functions.
- Product
- Spreadsheet software
- Device & OS
- not provided
- Scenario
- Comparing two data lists to identify matching or missing values.
- Observed behavior
- Formulas like MATCH and ISNUMBER can be combined to return exact TRUE/FALSE or custom text based on whether a lookup value exists in the target array.
Ensure your lookup list does not contain leading or trailing spaces, as these can cause exact match formulas to fail. You can use the TRIM function to clean your data first if necessary.
Use ISNUMBER and MATCH for Exact Existence Checks
This is the most robust method to check if a value exists in another column, returning a custom "Exists" or "Does Not Exist" result.
The MATCH function searches for a specified item in a range of cells, and returns its relative position. When combined with ISNUMBER, it evaluates to TRUE if a match is found and FALSE if it returns an error.
Click on the cell where you want the result to appear (for example, cell C2 right next to your first list).
Type =IF(ISNUMBER(MATCH(A2,$B$2:$B$10,0)),"Exists","Does Not Exist"), replacing A2 with your lookup value and $B$2:$B$10 with your target list range.
Ensure your target range uses absolute references (the dollar signs like $B$2:$B$10) so the range remains fixed when you copy the formula down.
Press Enter, then click and drag the fill handle at the bottom-right corner of the cell down to apply the formula to the rest of your list.
Use COUNTIF for a Simpler Alternative
COUNTIF is a slightly simpler formula that counts occurrences. If the count is greater than zero, the value exists in the other list.
Easily Compare Data Lists in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas like MATCH, ISNUMBER, and COUNTIF. You can efficiently compare lists, handle large datasets, and analyze data for free.
- 1. Open your worksheet in WPS Spreadsheet: Launch WPS Office, open your spreadsheet, and locate the two columns you want to compare.
- 2. Input the formula: In a blank column next to your list, enter =ISNUMBER(MATCH(A2,$B$2:$B$10,0)) to return TRUE if the value exists, or FALSE if it does not.
- 3. Extend the formula: Double-click the fill handle on the cell's bottom-right corner to automatically apply the formula to all rows in your list.
Frequently Asked Questions
Why does the formula return "Does Not Exist" when the value is clearly in the list?
This usually happens due to mismatched formatting or hidden spaces. Use the TRIM function on both lists to remove extra spaces, or ensure both columns are formatted as text or numbers consistently. Values like "MRGYO" and "MGRYO" with slight spelling differences will also fail to match.
How can I highlight existing values instead of adding a new column?
You can use Conditional Formatting. Select your first list, go to 'Home' > 'Conditional Formatting' > 'New Rule', choose 'Use a formula to determine which cells to format', and enter =ISNUMBER(MATCH(A2,$B$2:$B$10,0)). Set a background color to highlight the matches.
What does the '0' mean at the end of the MATCH function?
The '0' in the MATCH formula MATCH(A2, $B$2:$B$10, 0) instructs the function to look for an exact match. If you omit it, the spreadsheet might return an approximate match, which can lead to incorrect results when checking for existence.
Can I check if values exist across different worksheets?
Yes. You can reference another worksheet in your formula. For example, =ISNUMBER(MATCH(A8, 'Input'!$Y$2:$Y$26, 0)) will search for the value of A8 in the specified range on the 'Input' worksheet.
