Check if value exists in range in Excel and Google Sheets

In this article, we will explore the most commonly used formulas in Excel and Google Sheets to check if a value exists in a column, row, or within a range of cells.

Whether you're an experienced user or just starting to learn about your favorite spreadsheet program, knowing how to quickly identify if a specific value is present or absent in a given range can save much time and effort in your data analysis. This can be particularly useful when working with large datasets or when conducting research. In Microsoft Excel and Google Sheets, there are several formulas to perform this task. With our step-by-step guide, you can easily apply them to your spreadsheets, regardless of the structure and size of your dataset.

• Excel: check if value exists in range
  • If value exists in column
  • If value occurs in row
  • If value appears in range
• More formulas to find lookup value in a range
• Google Sheets: if value exists in range
• Check if value exists in list - partial match
• Highlight values that are present in range

If value exists in range in Excel

To check if a certain value exists in a range of cells, and if it does then return some text, and if it doesn't then return another text, you can use a combination of the IF and COUNTIF functions.

IF(COUNTIF(range, value)>0, "Yes", "No")

In this formula, COUNTIF counts the occurrences of a given value in a range. If the count is greater than zero, the IF statement returns "Yes". If the value is not found within the range, the COUNTIF function brings zero, and IF outputs "No".

This generic formula works nicely in all possible scenarios.

Check if value exists in a column

To test if a value in C3 is present in column A (more precisely in the range A3:A20), you can use this formula:

=IF(COUNTIF($A$3:$A$20, C3)>0, "Yes", "No")

Please pay attention that we lock the range reference ($A$3:$A$20) by using the $ sign for the formula to copy correctly in cells D4:D7:

Check if value exists in a row

To find out if a value in B5 appears in the row B2:I2, the formula takes this form:

=IF(COUNTIF($B$2:$I$2, B5)>0, "Yes", "No")

Check if value exists in a range

To check if a specific value occurs in a 2D range, supply the corresponding range reference to the first argument of COUNTIF. For example:

=IF(COUNTIF($A$3:$B$11, D3)>0, "Yes", "No")

More formulas to find lookup value in a range

The IF COUNTIF formula is the easiest but not the only way to find if a certain value appears in a range. Below are a few alternative solutions.

ISNUMBER and MATCH

Another way to check if a value occurs in a list is using MATCH together with the ISNUMBER function. For example:

=IF(ISNUMBER(MATCH(C3, $A$3:$A$20, 0)), "Yes", "No")

The MATCH function returns the position of the lookup value (C3) within the range (A3:A20) as a number; an #N/A error if no match is found. The ISNUMBER function converts a number to TRUE and an error to FALSE. Wrapping the construction into IF gives "Yes" or "No" as the final result.

ISNA and VLOOKUP

A slightly more complex but still working solution is using the IF, ISNA and VLOOKUP functions in one formula:

=IF(ISNA(VLOOKUP(C3, $A$3:$A$20, 1, FALSE)), "No", "Yes")

Here, the VLOOKUP function searches for the lookup value is the specified array and returns an exact match (FALSE) from the same column (1). If no match is found, an #N/A error is returned. (In Excel 365 and Excel 2021, you can use XLOOKUP instead of VLOOKUP.) The ISNA function checks the result for #N/A errors and returns TRUE if positive, FALSE otherwise. Finally, the IF function returns "No" for TRUE (no match) and "Yes" for FALSE (match).

For more information, please see How to use ISNA with VLOOKUP in Excel.

ISNA and MATCH

This solution is very similar to the previous one except that you utilize MATCH instead of VLOOKUP to check the ranges for matches:

=IF(ISNA(MATCH(C3, $A$3:$A$20, 0)), "No", "Yes")

Tip. If you goal is to find a lookup value in one column and return a matching value from another column, then use the VLOOKUP or XLOOKUP function in its basic form.

If value exists in range in Google Sheets

In Google Sheets, you can check if a value exists in a range using exactly the same formulas that we used in Excel.

For instance, to find whether the value in D3 occurs in the range A3:B11, the formula in E4 is:

=IF(COUNTIF($A$3:$B$11, D3)>0, "Yes", "No")

You can find a few more formulas in our sample spreadsheet, which is available at the end of this tutorial.

Check if value exists in range - partial match

To see if any cell in a range contains a given substring, place a wildcard character (asterisk) on both sides of the lookup value so that COUNTIF looks for it anywhere in a cell:

IF(COUNTIF(range, "*value*")>0, "Yes", "No")

For example, the bellow formula will check if any cell in the range A3:B11 contains the word "apple":

=IF(COUNTIF(A3:B11, "*apple*")>0, "Yes", "No")

If you are referring to a certain cell instead of a hardcoded value, then concatenate asterisks before and after the cell reference like this:

=IF(COUNTIF($A$3:$B$11,"*"&D3&"*")>0, "Yes", "No")

Highlight values that exist in range

A more visual way to identify values that exist in a given range could be highlighting them with Excel Conditional Formatting. The steps are:

1. Select the cells with values that you want to find in a range (C3:C7 in our case).
2. On the Home tab, in the Styles group, click Conditional Formatting > New Rule.
3. In the New Formatting Rule window, select Use a formula to determine which cells to format.
4. Enter one of these formulas in the Format values where this formula is true box:

   =MATCH(C3, $A$3:$A$20, 0)

   =COUNTIF($A$3:$A$20, C3)>0

   Where C3 is the topmost cell with the target values and $A$3:$A$20 is the range to check.

5. Click the Format… button and configure the desired formatting.
6. Click OK twice to close both dialog windows.

Done! All the values that exist in the range (exact match) are highlighted:

To highlight partially matching values, modify the formula like shown below - concatenate an asterisk on both sides of the lookup value reference:

=MATCH("*"&C3&"*", $A$3:$A$20, 0)

=COUNTIF($A$3:$A$20, "*"&C3&"*")>0

Tip. The same formula can be used with Google Sheets Conditional Formatting to highlight exact or partial matches in a range in Google spreadsheets.

Now you are equipped with the knowledge and skills to check if a value exists in a column, row or range with confidence and ease. Thank you for reading!

Practice workbooks

Check if value exists in range in Excel (.xlsx file) Find if value exists in range in Google Sheets (online sheet)

The above is the detailed content of Check if value exists in range in Excel and Google Sheets. For more information, please follow other related articles on the PHP Chinese website!