Home > Excel Formulas > Check IF a Value Exists in a Range
Contents hide
Check for a Value in a Range
How this Formula Works
Check for a Value in a Range Partially
Get the Excel File
Related Formulas
In Excel, to check if a value exists in a range or not, you can use the COUNTIF function, with the IF function. With COUNTIF you can check for the value and with IF, you can return a result value to show to the user. i.e., Yes or No, Found or Not Found.
Check for a Value in a Range
In the following example, you have a list of names where you only have first names, and now, you need to check if “Arlene” is there or not.
You can use the following steps:
- First, you need to enter the IF function in cell B1.
- After that, in the first argument (logical test), you need to enter the COUNTIF function there.
- Now, in the COUNTIF function, refer to the range A1:A10.
- Next, in the criteria argument, enter “Glen” and close the parentheses for the COUNTIF Function.
- Additionally, use a greater than sign and enter a zero.
- From here, enter a comma to go to the next argument in the IF, and enter “Yes” in the second argument.
- In the end, enter a comma and enter “No” in the third argument and type the closing parentheses.
The moment you hit enter it returns “Yes”, as you have the value in the range that you have searched for.
=IF(COUNTIF(A1:A10,"Glen")>0,"Yes","No")Want to learn more? Check out this Free Courses: 50 Excel Formulas to Get Smarter than Your Colleagues
How this Formula Works
This formula has two parts.
In the first part, we have COUNTIF, which counts the occurrence of the value in the range. And in the second part, you have the IF function that takes the values from the COUNTIF function.
So, if COUNTIF returns any value greater than which means the value is there in the range IF returns Yes. And if COUNTIF returns 0, which means the value is not there in the range and it returns No.
Check for a Value in a Range Partially
There counts to be a situation where you want to check for partial value from a range. In that case, you need to use wildcard characters (asterisk *).
In the following example, we have the same list of names but here is the full name. But we still need to look for the name “Glen”.
The value you want to search for needs to be enclosed with an asterisk, which we have used in the above example. This tells Excel, to check for the value “Glen” regardless of what is there before and after the value.
=IF(COUNTIF(C1:C10,"*"&"Glen"&"*")>0,"Yes","No")Get the Excel File
Related Formulas
FAQs
Check IF a Value Exists in a Range / Excel Formula? ›
In Excel, to check if a value exists in a range or not, you can use the COUNTIF function, with the IF function. With COUNTIF you can check for the value and with IF, you can return a result value to show to the user. i.e., Yes or No, Found or Not Found.
How do you check if a value already exists in Excel? ›- Open WPS Excel /Spreadsheet file where you want to check if a value exists in list in excel.
- Click on the cell where you want your output to reflect whether a value exists in list.
- Type “=ISNUMBER(MATCH” and press Tab. ISNUMBER Function with embedded MATCH Function will be initiated. ...
- Then you press enter.
- Open google sheets on your computer.
- In a new cell write the COUNTIF formula.
- Formula is =COUNTIF(range, criteria to check)
- Here range is where you want to search.
- Criteria is the text or cell to search.
- Press enter and the COUNTIF function will return the number of occurrences.
- Select the range of cells that you want to search. ...
- On the Home tab, in the Editing group, click Find & Select, and then click Find.
- In the Find what box, enter the text—or numbers—that you need to find.
- =IFERROR(IF(MATCH(D3,$A$2:$A$17,0),"Yes"),"No")
- =VLOOKUP (lookup_value, table_array, column_index_number, [range-lookup])
- =VLOOKUP(D3,$A$2:$B$17,2,FALSE)
- =IFERROR(VLOOKUP(D3,$A$2:$B$17,2,FALSE),"")
- =IFERROR(IF(VLOOKUP(D3,$A$2:$B$17,2,FALSE),"Yes"),"No")
Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. For example: =IF(A2>B2,"Over Budget","OK") =IF(A2=B2,B4-A4,"")
How do you check if a value is between a range in Excel? ›- Use the greater then (>) operator to check if the value is higher than a smaller number.
- Use the less than (<) operator to check if the value is lower than a larger number.
The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.
How do you assign a value based on a number range in Excel? ›Now, in cell C2, click on an empty cell and enter the formula as follows: =IF(AND(B2>=0,B2<=200),"D",IF(AND(B2>200,B2<=300),"C",IF(AND(B2>300,B2<=500),"B",IF(AND(B2>500),"A",0)))) and click on Enter to get the first result, as shown in the below image.
How do you check if a value is between two numbers in sheets? ›Type the formula “=ISBETWEEN”, into the cell. Input a value you want to compare, the upper and lower ends of the range, by directly typing in the formula or by cell references. Determine if the range includes or excludes the upper and lower ends. Press Enter to see the result of the formula.
How do you find the range of a formula in sheets? ›
For the statistical range of a data set from A1 to A10, for example, the formula would be =(MAX(A1:A10) – MIN(A1:A10)).
How do you find the value of a cell in sheets? ›In Google Sheets, the formula INDEX() allows you to return the value of a cell by specifying which row and column to look at in the specified array. =INDEX(A:A,1,1) for example will always return the first cell in column A.
How do you check if a cell contains a value? ›To check if a cell contains text, select the output cell, and use the following formula: =IF(ISTEXT(cell), value_to_return, ""). For our example, the cell we want to check is A2, and the return value will be Yes. In this scenario, you'd change the formula to =IF(ISTEXT(A2), "Yes", "").
How do you check if a cell has a value? ›To check if a cell value is a number or not, you can use IF + ISNUMBER in a combination. In this combination ISNUMBER tests if a value is a number or not and returns the result as TRUE and FALSE. After that, IF uses that TRUE or FALSE to return a meaningful value in the result.
How do I find all cells with a specific value in Excel? ›Use the Go To command to quickly find and select all cells that contain specific types of data, such as formulas. Also, use Go To to find only the cells that meet specific criteria,—such as the last cell on the worksheet that contains data or formatting.
How do I check if a cell value exists in a column? ›You can use the MATCH() function to check if the values in column A also exist in column B. MATCH() returns the position of a cell in a row or column. The syntax for MATCH() is =MATCH(lookup_value, lookup_array, [match_type]) . Using MATCH, you can look up a value both horizontally and vertically.
How do you check if a value exists in multiple columns in Excel? ›- Open WPS Excel /Spreadsheet file where you want to find matching values in two different columns in excel.
- Click on the cell where you want your output to reflect. Type “=IF(ISNUMBER(MATCH” and press Tab. ...
- Then you press enter. Now you drag this formula for each entry of column.
We use the ISNUMBER function to test if a value is a number. It will return “true” when the value is numeric and “false” when non-numeric. Let us consider the syntax “=ISNUMBER(G1).” It returns “true” if the argument “G1” contains a number or a formula that returns a numeric value.
Can you do a VLOOKUP with an if statement? ›One of the most common scenarios when you combine If and Vlookup together is to compare the value returned by Vlookup with a sample value and return Yes / No or True / False as the result. Translated in plain English, the formula instructs Excel to return True if Vlookup is true (i.e. equal to the specified value).
Can you use the IF function in Excel with text? ›Excel IF function with text
Commonly, you write an IF statement for text values using either "equal to" or "not equal to" operator. Translated into plain English, the formula says: return "No" if B2 is equal to "delivered", "Yes" otherwise.
What does <> mean in Excel? ›
In Excel, <> means not equal to. The <> operator in Excel checks if two values are not equal to each other.
What is the difference between VLOOKUP and Xlookup? ›To recap, the key differences between an XLOOKUP and a VLOOKUP are: XLOOKUP can look for values to the left and right of the lookup array, while VLOOKUP is limited to only looking for values to the right of the lookup value column.
How do you return a value if lookup value is in a range? ›Return a value if a given value exists in a certain range by using a formula. Please apply the following formula to return a value if a given value exists in a certain range in Excel. 1. Select a blank cell, enter formula =VLOOKUP(E2,A2:C8,3, TRUE) into the Formula Bar and then press the Enter key.
Which formula will allow you to use Excel to get a value from a list based on position? ›This is the basic use of the INDEX function and a simplest formula to make. To fetch a certain item from the list, you just write =INDEX(range, n) where range is a range of cells or a named range, and n is the position of the item you want to get.
How do I check if two cells have the same value in Excel? ›In cell “C1,” type the following formula: =IF(A1=B1, “Match”, “”), and you'll see “Match” next to the cells that have duplicate entries. To check for differences, you should type the following formula: =IF(A1<>B1, “No match”,” “). Again, use the fill handle by dragging it down to apply the function to all cells.
How do I find all cells with a specific value in sheets? ›To find all cells with value in Google Sheets, we will be using the COUNTIF function. The COUNTIF function is used to determine how many cells satisfy a requirement. In this example, we want to find call the cells that contain the word “Red”. We will simply insert this formula =COUNTIF(A2:A7,”Red”).
How do you check if a cell contains a number in sheets? ›What is the ISNUMBER Google Sheets Function? ISNUMBER is a function that tells Google Sheets if a cell contains a number. It returns TRUE if the value is a number and FALSE for any other value, including numbers in quotations and operations.
How do I find a value in all sheets in Excel? ›Search by selected worksheet
Highlight each worksheet tab you want to search by holding down Ctrl and clicking each tab you want to search. Once each worksheet you want to search is highlighted, perform a Find, and all highlighted worksheets will be searched.
- Select the cell that you want to evaluate. ...
- On the Formulas tab, in the Formula Auditing group, click Evaluate Formula.
- Click Evaluate to examine the value of the underlined reference. ...
- Continue until each part of the formula has been evaluated.
- To see the evaluation again, click Restart.
The ISTEXT in Excel is categorized under Information functions in the Formulas tab. It returns TRUE if the given value is a text, and FALSE if the cell contains any other value or is empty. You can use the ISTEXT function to check if a cell contains a text, numeric, date, or any other value.
How do you check if a value is repeated in Excel? ›
- Select the cells you want to check for duplicates. ...
- Click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- In the box next to values with, pick the formatting you want to apply to the duplicate values, and then click OK.
Use the COUNTIF function to count how many times a particular value appears in a range of cells.
How do you check if the same value exists in two columns Excel? ›Navigate to the "Home" option and select duplicate values in the toolbar. Next, navigate to Conditional Formatting in Excel Option. A new window will appear on the screen with options to select "Duplicate" and "Unique" values. You can compare the two columns with matching values or unique values.
How do you count the same or duplicate values only once in a column? ›- Select the range of cells, or make sure the active cell is in a table. ...
- On the Data tab, in the Sort & Filter group, click Advanced. ...
- Click Copy to another location.
- In the Copy to box, enter a cell reference. ...
- Select the Unique records only check box, and click OK.
- Select the data.
- Go to Home –> Conditional Formatting –> Highlight Cell Rules –> Duplicate Values.
- In the Duplicate Values dialog box, select Duplicate in the drop down on the left, and specify the format in which you want to highlight the duplicate values.
Open the workbooks you want to compare. Go to the View tab, Window group, and click the View Side by Side button. That's it!
How do you check if a value is in two columns? ›In Excel, you can compare two columns using the IF condition. The formula to compare two columns is =IF(A2=B2,”Match”,” ”). It returns the result as Match against the rows that contain matching values, and the remaining rows are left empty.
How do I compare two lists in Excel? ›- Immediately after the two columns, we must insert a new column called “Status” in the next column.
- Now, we must put the formula in cell C2 as =A2=B2.
- This formula tests whether the cell A2 value is equal to cell B2.
To filter for unique values, click Data > Sort & Filter > Advanced. To remove duplicate values, click Data > Data Tools > Remove Duplicates. To highlight unique or duplicate values, use the Conditional Formatting command in the Style group on the Home tab.