The Excel If In List Guide That Makes Life Easier
Jun 01, 2023
Are you tired of struggling with lists, hoping to get the right value to get you organized and productive?
You're not alone in your plight! Thankfully, there is a powerful tool out there that can help – Microsoft Excel. After all, this tool has become the trusted ally of many professionals worldwide when tackling complex list-based tasks.
Today, we'll show you how to get specific data in a list using certain Excel functions.
Read on as we cover the following:
-
Sample Data of Check if a Value is in the List in Excel
-
Check if a Value Is in the Column with the Find and Select Tool in Excel
-
Check if a Value Is in the column with the COUNTIF Function in Excel
-
Check if a Value Is in the column with ISNUMBER and MATCH Functions in Excel
-
Check if a Value is in the column with VLOOKUP Function in Excel
-
Final Thoughts on Excel If In List
-
Frequently Asked Questions on Excel If In List
Sample Data of Check if a Value Is in the List in Excel
In the sample data below, we have provided information about various products from a company. We will use this table to show how to easily check for a value in a list.
For example, we will use the Product column as the list.
Check if a Value Is in the Column with the Find and Select Tool in Excel
To find the product "Strawberry" in the range list below, you can use the "Find & Select" option.
-
Click the Home tab, and under the Editing groups, click the Find & Select tool, then choose the Find option.
-
In the Find and Replace box, type "Strawberry" (no quotes) and click Find All.
After completing the steps, you'll get the cell reference of the product Strawberry in the list.
Check if a Value Is in the Column with the COUNTIF Function in Excel
To check the items in the Product list, you may use the COUNTIF function.
-
Select a cell, then type the following formula shown in the image.
-
Press the Enter key, then use the Flash fill tool to get the result of the other blank cell.
If the COUNTIF function finds the value in the list, it will return a number indicating the number of times the value appears.
Meanwhile, if the value appears in the list, it will result in TRUE. Otherwise, it is not in the list if it returns a FALSE value.
Check if a Value Is in the Column with the ISNUMBER and MATCH Functions in Excel
Let's say we want to compare the items in the "Item" column to those in the "Product" column.
The outcome "Result" column will be displayed once you use the ISNUMBER and MATCH functions.
-
Select a blank cell.
-
Enter the ISNUMBER and MATCH formulas.
If there is a number, the function ISNUMBER will return TRUE. If there is no number, it will return FALSE.
The MATCH function returns a value in a column or array and gives its relative position based on the selected exact match type. If the function cannot find the value, it will return a #NA error.
Check if a Value Is in the Column with the VLOOKUP Function in Excel
The VLOOKUP function can help you find something in a table. It finds a value in the first column and then gives you the right information from the same row but from another column index number.
You will know that the value is in the column if it returns as value. However, getting an #NA Error means the value is not in the column.
VLOOKUP syntax:
For example, search for the region where the Blackberry originates from a table's second column. To find the corresponding region, we will use the VLOOKUP function.
-
Type your VLOOKUP formula.
-
Press the Enter key to get the outcome of the VLOOKUP function.
With the VLOOKUP function, we'll know that the region of the origin of Blackberry is in the list.
Final Thoughts on Excel If In List
Understanding whether data in a list can significantly impact your business success and personal well-being is crucial. Thankfully, Excel provides useful functions that enable you to move swiftly, take confident actions, and support them with data.
Visit Simple Sheets for more easy-to-follow guides, and remember to check out the related articles section of this blog post.
For the most straightforward Excel video tutorials, subscribe to Simple Sheets on Youtube!
Frequently Asked Questions on Excel If In List
How does the IF function help the MATCH function determine if the value is in the list?
The MATCH function will check the IF function to return "Yes" if it finds a value in a relative position. However, if the MATCH function returns a #NA error, the IFERROR function will replace it with "No."
How to apply conditional formatting if the data is in the column?
To apply conditional formatting, follow these steps:
-
Go to "Conditional Formatting," select "Highlight Cells Rules," and choose "Equal to." A dialog box will appear.
-
Insert the value you need to search for in the column.
-
Select your preferred formatting style from the next drop-down list and click "OK" to confirm the changes.
What functions should I use to check multiple values in a column?
You can use a combination of functions, including IFERROR, INDEX, SMALL, IF, COUNTIF, and MATCH.
Related Articles:
Excel: Remove Trailing Spaces Quickly and Easily With These Simple Steps
Want to Make Excel Work for You? Try out 5 Amazing Excel Templates & 5 Unique Lessons
We hate SPAM. We will never sell your information, for any reason.
