Spreadsheet Practice

How to check if a value exists in a list in Excel

Formula (Excel and Google Sheets)

=ISNUMBER(MATCH(D1,A2:A9,0))

Use =ISNUMBER(MATCH(D1,A2:A9,0)): MATCH finds a position if the value is there, and ISNUMBER turns that into TRUE or FALSE.

Result on the example
TRUE in D2
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.

How it works

MATCH returns the vendor's position, 2, when it is in the list and #N/A when it isn't. ISNUMBER is TRUE for a position and FALSE for the error, so the formula always gives a clean TRUE or FALSE.

=ISNUMBER(MATCH(D1,A2:A9,0))

ResultTRUE

  1. D1"Bluewater Supply"

    The value in D1.

  2. A2:A98 rows × 1 col: "Acme Industrial", "Bluewater Supply", "Cedar Logistics", "Delta Packaging", …

    A range: 8 cells in one column.

  3. MATCH(D1,A2:A9,0)2

    MATCH: Position of a value in a list. 0 = exact match.

    • lookup_valueD1"Bluewater Supply"
    • lookup_arrayA2:A98 rows × 1 col: "Acme Industrial", "Bluewater Supply", "Cedar Logistics", "Delta Packaging", …
    • match_type0
  4. ISNUMBER(MATCH(D1,A2:A9,0))TRUE

    ISNUMBER: TRUE if the value is a number.

    • valueMATCH(D1,A2:A9,0)2

In Google Sheets

The same formula works in Google Sheets: =ISNUMBER(MATCH(D1,A2:A9,0)). We ran it in Google Sheets mode on the same data and it returns TRUE.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=COUNTIF(A2:A9,D1)>0TRUECounts the vendor in the list. Also works across a block of several columns.
=IF(COUNTIF(A2:A9,D1),"Approved","Not approved")ApprovedDifferent resultReturns a label instead of TRUE or FALSE. Shows "Approved".

Common mistakes

  • Both tests ignore case, and a trailing space makes "Bluewater Supply " a different value.
  • COUNTIF treats * and ? as wildcards, so a value containing them can be counted wrongly. MATCH with 0 does too; EXACT doesn't.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Check if a value exists in a list

In D2, return TRUE if the vendor in D1 is on the approved list in A2:A9, and FALSE if it isn't.

Questions

How do I check for part of the text?

Use wildcards: =COUNTIF(A2:A9,"*"&D1&"*")>0.

How do I check a whole column of values against the list?

Fill =ISNUMBER(MATCH(D2,$A$2:$A$9,0)) down, or in Excel 2021 or Microsoft 365 use =ISNUMBER(MATCH(D2:D20,A2:A9,0)).

How do I do a case-sensitive check?

Use EXACT: =SUMPRODUCT(--EXACT(A2:A9,D1))>0.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.