Check If a Value Exists in a List in Excel

To check if a value exists in a list in Excel, count how many times it appears and test whether the count is above zero: =COUNTIF(A2:A8,D2)>0. The result is TRUE or FALSE, and you can wrap it in IF to show "Yes" or "No". This guide covers that formula, the ISNUMBER and MATCH alternative, XLOOKUP for returning related data, and the small data problems that make Excel say a value is missing when you can see it.

The quickest way: COUNTIF greater than zero#

COUNTIF counts the cells that match a value. If the count is more than 0, the value exists. All the examples use this approved-vendor list in A1:A8, with a vendor to check in D2:

In E2, type:

=COUNTIF(A2:A8,D2)>0

The result is TRUE, because Cobalt Co is in row 4. Change D2 to "Harbor Group" and it becomes FALSE. COUNTIF works in every version of Excel and isn't case-sensitive, so "cobalt co" also returns TRUE. The COUNTIF guide and the COUNTIF function reference explain the criteria rules.

How to return Yes or No instead of TRUE or FALSE#

Wrap the test in IF. IF returns the second argument when the test is TRUE and the third when it's FALSE:

=IF(COUNTIF(A2:A8,D2)>0,"Yes","No")

With Cobalt Co in D2, this returns Yes. You can return any text: "Approved" and "Not approved", or "In stock" and "Missing".

To check a whole column of values against the list, lock the list with $ signs and fill down. If the vendors to check are in D2:D20:

=IF(COUNTIF($A$2:$A$8,D2)>0,"Yes","No")

Fill it down with Ctrl+D on Windows or ⌘D on a Mac. Then filter for "No" to see every vendor that isn't approved. For side-by-side list checks, the guide on how to compare two lists in Excel goes further.

How to check with ISNUMBER and MATCH#

MATCH returns the position of a value in a list, or #N/A if it isn't there. ISNUMBER turns that into TRUE or FALSE:

=ISNUMBER(MATCH(D2,A2:A8,0))

For Cobalt Co, MATCH(D2,A2:A8,0) returns 3 (it's the third item), so ISNUMBER returns TRUE. For a missing vendor, MATCH returns #N/A and ISNUMBER returns FALSE. The 0 at the end means exact match. Leave it out and MATCH assumes a sorted list and may return a wrong position.

This pattern is common in job tests and older workbooks. The guide to the MATCH function explains the match types, and the MATCH and ISNUMBER function guides cover the syntax.

COUNTIF or MATCH? Both give the right answer for normal lists. COUNTIF is easier to read. MATCH is useful when you also need the position, for example to feed INDEX.

How to return related data with XLOOKUP#

When you want more than Yes or No, use XLOOKUP with a not-found message. Say you add each vendor's account manager in B2:B8:

=XLOOKUP(D2,A2:A8,B2:B8,"Not on the list")

If D2 is on the list, XLOOKUP returns the manager's name. If it isn't, it returns "Not on the list" instead of #N/A. XLOOKUP needs Excel 2021 or Microsoft 365. The article on XLOOKUP's if-not-found argument shows more ways to use that fourth argument.

How to check for part of the text or exact capitals#

Add wildcards to find cells that contain the value. The * stands for any number of characters:

=COUNTIF(A2:A8,"*"&D2&"*")>0

With "Supply" in D2, this returns TRUE because of "Delta Supply". It's useful when names have extra words, like "Ltd" or "Inc".

For a case-sensitive check, use EXACT, which compares capitals too:

=SUMPRODUCT(--EXACT(A2:A8,D2))>0

EXACT returns TRUE or FALSE for each cell, the -- turns those into 1 and 0, and SUMPRODUCT adds them. With "COBALT CO" in D2, COUNTIF says TRUE but this formula says FALSE.

Why Excel says No when the value is there#

When you can see the value but the formula disagrees, the two cells aren't identical. Check these in order:

1. Extra spaces. "Cobalt Co " with a trailing space is a different value. Clean both sides with TRIM, or test with =LEN(A4) to spot hidden characters.
2. Numbers stored as text. An ID of 1001 typed as text may not match the number 1001. COUNTIF treats them as the same, but MATCH and XLOOKUP don't, so MATCH returns #N/A. Convert the text with VALUE or Data > Text to Columns.
3. Wildcard characters in the data. COUNTIF and MATCH read * and ? as wildcards. To find a literal asterisk, put a tilde before it: "~*".
4. The wrong range. A list that grew past row 8 isn't checked. Use a whole column (A:A) or an Excel table so the range grows.

If MATCH or XLOOKUP still returns #N/A, the guide on how to fix the #N/A error walks through every cause.

How to highlight values that exist in another list#

Use the same COUNTIF test as a conditional formatting rule. Select the values to check (for example D2:D20), then go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format and enter:

=COUNTIF($A$2:$A$8,D2)>0

Pick a fill colour and click OK. Every vendor on the approved list gets that colour. Change >0 to =0 to highlight the ones that are missing instead.

Practise it#

"Is this value in the list?" comes up in data cleaning, reconciliations and interview tasks. Practise COUNTIF in a live spreadsheet to get the count-based check into your fingers, then practise XLOOKUP with a not-found message, and finish with fixing lookup errors to diagnose #N/A and false misses. Each lesson checks your formula instantly. Or start with the exercise on this page.