by 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.
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:
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:
- Select the cells with values that you want to find in a range (C3:C7 in our case).
- On the Home tab, in the Styles group, click Conditional Formatting > New Rule.
- In the New Formatting Rule window, select Use a formula to determine which cells to format.
- 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)>0Where C3 is the topmost cell with the target values and $A$3:$A$20 is the range to check.
- Click the Format… button and configure the desired formatting.
- 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)
8 comments
I want to highlight complete selected row, If value match with other sheet single column.
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
Hello John,
Since you mentioned IMPORTRANGE, I assume you work in Google Sheets. If so, I believe the QUERY function will help you with your task.
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")
Hi! If I understand your task correctly, try the following formula:
=IF(AND(C3>0.01,C3<=0.20),"F40")
You can also find useful information in this article: Nested IF in Excel – formula with multiple conditions.
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!
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.