Check if value exists in range / column 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.

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 column in Excel.

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 row in Excel.

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") Check if value exists in a range in Excel.

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). The ISNA and VLOOKUP formula to find if a value is present in a rage.

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") If value exists in range in Google Sheets.

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") Check if a substring exists in range (partial match).

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: Highlight values that exist in range.

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 Highlight partially matching values.

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)

Table of contents

24 comments

  1. thank you so much :)

  2. Hi,

    Thank you for so many solutions that I have referred here for. I now need a solution for the below scenario.

    I need to refer a cell value, which is a combination of text and numbers (eg. 22AIPL501) and marks for that cell to another set of data, which has the typical values (eg. 22AIPL501, 22MEPC601, etc.) in the column 1, column 2 has minimum mark value, column 3 has maximum mark value and column 4 has grade value for the range of marks. The return value should be corresponding grade value for the mark from first data. Kindly help to solve this. Thanks in advance.

  3. Hi 👋 I have 5 columns, in column A i have repeatative dates, in column B i have vehicles number(100s of vehicles coming every day), in column C i have quantities of material which vehicles are carrying, in column D i have dates again(on which the specific vehicles get late), in column E i have vehicles number which are late.

    Now i want a formula to check the date and vehicle number from column D and Column E and match it with column A and column B and if both date and vehicle numbers get match then in a new column revert the quantity from column C and also i want to highlight rows from A1 to E1

  4. Hi,
    Can you remind me the formula to use when I need information from two columns to be true?
    For example, Column A in my spreadsheet has the status of something (i.e., Completed)
    Column B has the data it was completed (i.e., 12 April 2024)

    I want to write a formula that will count for me:
    Status from Column A = Completed
    AND
    Status from Column B = (date in the month of April 2024)

    So essentially a date range from April 1-30 and status completed.
    I can get these independent of one another but having a hard time doing one formula with both.

    I am working in Google Sheets.

    Thank you so much!!

  5. Hi I’m trying to highlight the sales rep name who has the maximum cogs. I have selected the range B5:I5 which has the sales rep names. Then I’m trying to create a formula with conditional formatting but don’t know which formula to use if I am trying to match B25 which has the maximum cogs to row 20 which has the grand total with from all the sales rep associates.

  6. In my Google Sheet, I have a column for the number of students and a column for the scores the students received on a test. Students can receive a 4, 3, 2, or 1 on the test and I am using the =COUNTIFS(C5:C33,"=4") function to find out how many "4s" I have in the column. This data is placed in a row called "Received a 4." If, for instance, 18 of the students received a 4 on the test, then the "Received a 4" row will show the figure 18.

    The problem I run into and am trying to fix is in a different row, when I want to find the % of my students (that took the test) that received a 4 on the test. Currently, I am using this formula -- =Round(C35/$A$33, 2), but it takes into account all 29 of my students. Sometimes, on a test, not all 29 students will be present. What I would like the Round cell to compute is, if a student was present, its total will be used in the computation of the %. If a student is not present and a score is not entered into the system for that student, then he/she will not be counted in the total that gives me the percentage.

    Can anyone help me with a formula that will take into account only cells that have information in them, instead of using all of the students when I try to compute the percentage?

    Thank you for all of your help.

    Trigg

    • Hello Trigg,

      I replied to your comment under this article for Google Sheets regarding your task with students' grades. Have you tried that solution? Or has your task gotten more complicated? Please let me know if you still need help with it.

  7. Hello!
    I use google sheets for work and am not well versed in formulas, I am hoping someone will be able to help me.

    I am wondering if there is a way to mark a checkbox as true in one sheet if it is checked off as true in a master list.

    For example, if John's treatment plan for the month of October is completed, I would like to check it off only once from the master list, and it will automatically check off the Treatment Plan column for John every time he is seen in the month.
    I hope that is easy to understand, I would appreciate any help. Thank you.

    • Hello Garcia,

      If I understand your task correctly, you need to use the IF function to check whether the checkbox in the master list is TRUE or FALSE, like this:
      =IF(MasterSheet!A2=TRUE,TRUE,FALSE)

      And then insert a checkbox to a cell with this IF formula as well.

  8. Hi!

    I have a main overview tab in which I want to point to a lot of different tabs. The overview tab contains the overview of all products, products which all have a seperate tab in the same spreadsheet.

    What I am trying to do is have a cell return 'yes' or 'no', based on the values in an entire column of another tab. So, I want one cell to check in a different tab, check a column (B) in that tab, and return 'yes' if all values in column B are 'yes', but return 'no' if it finds just one 'no' in column B.

    I hope I was able to explain the situation well, and I hope you are able to help me proceed! Thank you.

  9. I have a long list of dates in column A and a long list of decreasing loan balance values in Column C. I’m looking for a formula that will provide the date in column A that corresponds to the column C value when it falls between 0 and 1180, there will only be one row that column C meets that value range.

  10. I want to highlight complete selected row, If value match with other sheet single column.

  11. I haven't seen these types of creative solutions before. They don't quite fix my problem but I would be most appreciated if I could be pointed in the right direction. I need the row of a value to use for an indirect value formula. I need to find "First Quarter" in another sheet using importrange and the row that it is in. I am unsure how to do this as all my efforts have failed thus far. Thank you kindly in advance.

    John Mak

  12. Hi

    I am having troubles with an equation. Maybe someone is smart enough to help me.

    I want excel to look for a value in a cell that is in a range of numbers (for example 0.10 to 0.99) whenit picks up the number within the range it must display a specific word as well.

    The catch is that I will have to display these words according to the value found within the range.

    Cell C3 is a value that the user will punch in manualy. I want Cell D3 to tell me if C3 is a value within 0.01 and 0.20 then display F40. There will be multiple of these equations in one cell and I simply can't get them to work.
    Example =if(C3>0.01,C3<=0.20,"F40")

  13. Hi!

    Thanks for the reply! . . . I can't quite see where my description is lacking - if you can pinpoint the bit that's not clear, I'll try and expand it.

    Although - Your formula would work for me, if the list of dates was only 1 week long . . . but my list (column A) has dates from all weeks since 2011!, so a MAX equation would not work for any week other than the first (in your example).

    I am wanting to compare each Monday's figure vs. it's corresponding Wednesday's figure in the same week . . . but to do this over and over for the full (since 2011) list.

    It may be more understandable to imagine that Column A is a list of dates, and Column B is a list of stock market close prices for that date. Column C is the day of the week (Mon, Tues, Weds etc.) derived from Column A's dates . . . and what I'm trying to do is see if I bought stocks on Mon, and sold on Weds, whether that provides an overall better result than if I bought on Tues and sold on Weds, for all the corresponding weeks in my list of data.

    Trying to discover if there's a pattern that appears by buying/selling on specific days of the week!

  14. Hi - I doubt this is exactly the correct spot to ask this particular question, but I can't find a better title to suit, so here goes . . .

    I have a long list of dates in column A, and a corresponding list of daily sales figures in column B. I use the 'Text(A1,"dddd") function to populate column C with the actual day of the week that corresponds to the date in column A.

    Looks like this :

    10/1/2010 123.4 Monday
    11/1/2010 213.4 Tuesday
    12/1/2010 342.1 Wednesday
    13/1/2010 421.3 Thursday
    14/1/2010 214.3 Friday
    etc. etc.

    I have made a 'selector' and populated column D with the sales data from column B IF the day of the week corresponds to my selection. So if my selector is set to 'Monday', column D would show 123.4

    Like this :

    10/1/2010 123.4 Monday 123.4
    11/1/2010 213.4 Tuesday 0
    12/1/2010 342.1 Wednesday 0
    13/1/2010 421.3 Thursday 0
    14/1/2010 214.3 Friday 0
    etc. etc.

    I have made another 'selector' and populated column E similarly to the above, but each selector can be set independently, and so columns D and E can show column B sales data from different days of the week . . . eg. column D would show Monday's sales data, and column E might show Wednesday's data (both replicated from column B).

    Like this :

    10/1/2010 123.4 Monday 123.4 0
    11/1/2010 213.4 Tuesday 0 0
    12/1/2010 342.1 Wednesday 0 342.1
    13/1/2010 421.3 Thursday 0 0
    14/1/2010 214.3 Friday 0 0
    etc. etc.

    Question is, can I perform sums/calculations between column D and column E, when they are not in row alignment. I want to (for example) see the difference between Wednesday's sales compared to Monday's sales for my entire (long) list of entries . . . in english(ish), it would be something like this :

    Where Day1 = Monday and Day2 = Wednesday, subtract Wednesday sales figure from Monday's sales figure and show the result in column F. Ideally it would look something like this :

    Like this :

    10/1/2010 123.4 Monday 123.4 0 0
    11/1/2010 213.4 Tuesday 0 0 0
    12/1/2010 342.1 Wednesday 0 342.1 218.7
    13/1/2010 421.3 Thursday 0 0 0
    14/1/2010 214.3 Friday 0 0 0
    etc. etc.

    I'm stuck and can't fathom any of my known (limited) Excel function knowledge to help me - any ideas??

    Thanks in advance!
    LM.

    • Hi!
      I’m sorry but your description doesn’t give me a complete understanding of your task. I assume that you have only chosen 2 dates.
      However, I’ll try to guess and offer you the following formula:

      =MAX(E1:E5)-MAX(D1:D5)

      If this is not what you wanted, please describe the problem in more detail.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)