[2024] Check if value is in list in Excel (3 easy methods)

While working on excel with lots of data, some times you want to check if a certain value exists in a list. This might seem a simple task when your list is small and you can check manually that whether the required value exists in that list. But when you are required to check a bigger list of data in to see excel value in list WPS (2016/2019/mac/online) then it becomes a very tough task and it might take you ages to perform this task. Luckily there are different shortcuts possible in excel to find if a value exists in list in excel.

This article covers different shortcut methods in excel value in list WPS (2016/2019/mac/online). Once you go through this article you will be able to easily identify if a value exists in a list. 4 different shortcut ways of checking if a value exists in a list in excel updated in 2023.

#1 Using "COUNTIF function" to check if a value exists in list in excel

Among different ways to check if a value exists in a list the first methods is using COUNTIF function. You can use COUNTIF formula to see if a value exists in a list by following simple below mentioned steps and picture illustrations.

Steps:

1. Open WPS Excel /Spreadsheet file where you want to check if a value exists in list in excel.

2. Click on the cell where you want your output to reflect whether a value exists in list.Type “=COUNTIF” and press Tab.

3. COUNTIF Function will be initiated.You need to enter two parameters in this function I.e. Range (list in which which you want to check if a value exists in it)Criteria (Here you enter the value inside inverted commas I.e. “xyz”)

Then you press enter.This function will return the exact number of times the required value exists in the selected list.

#2 Using "COUNTIF embedded in IF function" to check if a value exists in list in excel

Another way of checking if value exists in list is by using a COUNTIF function embedded in IF function. You can use this method to see if a value exists in a list by following simple below mentioned steps and picture illustrations.

Steps:

1. Open WPS Excel /Spreadsheet file where you want to check if a value exists in list in excel.

2. Click on the cell where you want your output to reflect whether a value exists in list.Type “=IF(COUNTIF” and press Tab.

3. IF Function with embedded COUNTIF Function will be initiated.You need to enter four parameters in this function I.e. Range (list in which which you want to check if a value exists in it)Criteria (Here you enter the value inside inverted commas I.e. “xyz”)Value IF True (You can use “Yes” here)Value IF False (You can use “No” here)

4. Then you press enter.This function will return whether a value exists in a list in simple yes or no terms instead of exact number of time as shown section 1.1.

#3 Using "MATCH function embedded in ISNUMBER function" to check if a value exists in list in excel

Another method of checking if a value exists in a list is to use MATCH Function Embedded in ISNUMBER function.

Steps:

1. Open WPS Excel /Spreadsheet file where you want to check if a value exists in list in excel.

2. Click on the cell where you want your output to reflect whether a value exists in list.

3. Type “=ISNUMBER(MATCH” and press Tab.ISNUMBER Function with embedded MATCH Function will be initiated.You need to enter three parameters in this function I.e. Lookup Value  (value that needs to be checked in a list)Look Up Array (list in which the value is to be checked)Match Type (This must be set to 0 to search for an exact match)

4. Then you press enter.This function will return whether a value exists in a list in simple True or False terms instead of exact number of time as shown section 1.1.

#4 Using "OR function" to Check If a Value is in List in Excel

Steps: 

1. Select cell F5 as the output cell.

2. In cell F5, enter the following formula: =OR($B$5:$B$10=E5)

• $B$5:$B$10 represents the range where you want to check for your desired value.

• E5 is the specific value you are searching for.

• The OR function will return TRUE if the value is found in the list, otherwise FALSE.

3. Press ENTER to calculate the formula.

4. Use the Fill Handle Tool to drag the formula down to fill the adjacent cells, if necessary.

Check if value is in list in Excel with WPS Office - Better Alternative to MS Office

WPS Office is a powerful and versatile office software that offers seamless compatibility with Microsoft Office file formats. It provides a familiar interface and functionality, allowing you to effortlessly create, edit, and collaborate on documents, spreadsheets, and presentations. Whether you're a student, professional, or entrepreneur, WPS Office caters to your diverse office requirements and can help to Check if value is in list in Excel easier.

Trustpilot4.8

WPS Office- Free All-in-One Office Suite

• Use Word, Excel, and PPT for FREE, No Ads.

• Edit PDF files with the powerful PDF toolkit.

• Microsoft-like interface. Easy to learn. 100% Compatibility.

• Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

Free Download

5,820,008 User

Algirdas Jasaitis

logo

Frequently Asked Questions (FAQs)

Q1: How do you check if a value is contained in a range in Excel?

A1: To check if value is in list Excel, you can use the COUNTIF function. Simply specify the range and the value you want to check, and if the count is greater than zero, it means the value is present in the range.

Q2: How do you check if a cell has a value in a spreadsheet?

A2: To check if a cell has a value in a spreadsheet, you can use the ISBLANK function. This function returns TRUE if the cell is empty and FALSE if it contains any value. By checking the result of the ISBLANK function, you can determine if the cell has a value or not.

Q3: How do I find a specific value in a column in Excel?

A3: To find a specific value in a column in Excel or check if a value is in a list, you can use the MATCH function. This function allows you to search for a value within a range and returns the position of the matched value. By specifying the column range and the value you want to find, you can locate the position of the desired value in the column.

Final Thought on Ways to check if Values in list in Excel

In conclusion, we have explored 4 easy methods updated in 2024 to check if a value is in a list in Excel. The VLOOKUP function, the COUNTIF function, and the MATCH function embedded in the ISNUMBER function provide efficient ways to validate data and identify the presence or absence of a specific value. These methods empower Excel users to streamline their data analysis and decision-making processes.

With the combination of Excel's versatile functions and the power of WPS Office and WPS Templates, you have the tools at your disposal to excel in your data analysis and document management tasks. Embrace these resources and elevate your productivity to new heights.