Nazmus Sakib
Returning the availability of a value in a column is a widely used operation in Excel. We can easily combine different functions to show whether a value is in a list. If the value exists in the column, then it will return TRUE in Excel with 5 convenient approaches in this article.
📁 Download Excel File
Download the following Excel workbook to realize the topic more clearly.
Learn to Return True If Value Exists in Column in Excel with These 5 Suitable Approaches
Today we will use a sample dataset to show TRUE if a value exists in a column in Excel which has a list of Products in Stock. We will check the Availability of the Ordered Products with the products in stock.
1. Showing True If Two Cells Contain Same Value
First of all, we will learn if the adjacent two cells contain the same value or not. We will show TRUE in the Availability column if the value matches.
1.1 Using Basic Formula
We will show TRUE if two cells contain the same value with a basic formula.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell D6 and Enter the formula below:
=B6=C6
- Next, use the Fill Handle icon from the bottom right corner of cell D6 and double-click.
- As a result, we will have an Availability list.
1.2 Applying EXACT Function
In our next approach, we will use EXACT function to show TRUE if the two adjacent cells contain the same value.
⬇️⬇️ STEPS ⬇️⬇️
- To begin with, Enter the formula below in cell D6:
=EXACT(B6,C6)
- After that, use the Fill Handle icon just like in the prior approach.
- Now, we will see the Availability in the D column.
📕 Read More: 5 Ways to Find First Occurrence of a Value in a Range in Excel
2. Combining ISNUMBER and MATCH Functions
In this approach, we will demonstrate how to show TRUE or FALSE if the value is present in the column by combining ISNUMBER and MATCH functions.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, select cell E6, type in the following formula, and press Enter.
=ISNUMBER(MATCH(D6,$B$6:$B$18,0))
- Later, use the Fill Handle icon and double-click.
- Voila! Consequently, we have shown if the Orders are available in Stock.
🔨 Formula Breakdown
ISNUMBER(MATCH(D6,$B$6:$B$18,0))
👉 The MATCH function searches and finds the Code stored in cell D6 from cells B6:B18 and returns the serial number. Here, it returns 3 as AC019 is the 3rd product in the Stock list.
👉 After that, the ISNUMBER function checks whether the value returned by the MATCH function is a number. If yes, it returns TRUE.
📕 Read More: 3 Ways to Check If Range of Cells Contains Specific Text in Excel
3. Merging NOT, ISERROR, and MATCH Functions
Now, we will merge NOT, ISERROR, and MATCH functions to show TRUE if the value exists in the column.
⬇️⬇️ STEPS ⬇️⬇️
- First, Enter the formula below in cell E6:
=NOT(ISERROR(MATCH(D6,$B$6:$B$18,0)))
- Afterward, use the Fill Handle icon and double-click to apply the formula to the whole column.
- There you go! We have shown TRUE if the ordered product is in stock.
🔨 Formula Breakdown
NOT(ISERROR(MATCH(D6,$B$6:$B$18,0)))
👉 Initially, MATCH function will return the serial number where it finds the value of cell D6 from cells B6:B18.
👉 Afterward, ISERROR function scrutinizes whether the MATCH function returns an error. If it is not an error, the function returns FALSE.
👉 Then, NOT function just reverses the argument returned by the ISERROR function.
📕 Read More: Excel Formula: Find Last Row Number with Data
4. Coalescing IF, ISERROR, and VLOOKUP Functions
Here, we will coalesce IF function with ISERROR function along with VLOOKUP function to show TRUE if the value exists in the column.
⬇️⬇️ STEPS ⬇️⬇️
- To start with, select cell E6 and type in the following formula, and press Enter:
=IF(ISERROR(VLOOKUP(D6,$B$6:$B$18,1,FALSE)),FALSE,TRUE)
- Next, use the Fill Handle icon.
- Done! Consequently, you have the list of available products.
🔨 Formula Breakdown
IF(ISERROR(VLOOKUP(D6,$B$6:$B$18,1,FALSE)),FALSE,TRUE)
👉 Firstly, VLOOKUP function searches the value of cell D6 from cells B6:B18. If it finds the value, it returns the value itself otherwise it returns #N/A error.
👉 Next, ISERROR function analyzes whether the value returned by the VLOOKUP function returns error. If it is an error, it returns TRUE.
👉 Later, the IF function will return FALSE if the ISERROR function returns TRUE. Else it will show TRUE.
5. Implementing IF and COUNTIF Functions
Lastly, we will implement the combo of IF and COUNTIF functions to show TRUE if the ordered products are available.
⬇️⬇️ STEPS ⬇️⬇️
- First of all, select cell E6 and write the formula below, then press Enter:
=IF(COUNTIF($B$6:$B$18,D6)>0,TRUE,FALSE)
- Next, use the Fill Handle icon and double-click.
- Great! Eventually, we know which products are available in our stock.
🔨 Formula Breakdown
IF(COUNTIF($B$6:$B$18,D6)>0,TRUE,FALSE)
👉 The COUNTIF function checks whether it finds the value stored in cell D6 from cells B6:B18 at least once. If it finds the value, it returns TRUE.
👉 If the COUNTIF function returns TRUE, the IF function will also return TRUE else it will return FALSE.
📝 Takeaways from This Article
📌 Firstly, we learned if two cells contain the same value and showed TRUE or FALSE by using an elementary formula.
📌 Secondly, we described how to use the EXACT function to show TRUE if the adjacent two cells contain the same value.
📌 Next, we used the combination of ISNUMBER and MATCH to show TRUE if the value exists in the column.
📌 Later, we demonstrated how to apply NOT, ISERROR, and MATCH functions to return TRUE or FALSE.
📌 Afterward, we returned TRUE if the value exists in the column by merging IF, ISERROR, and VLOOKUP functions.
📌 Finally, we learned how to use IF and COUNTIF functions to return TRUE.
Conclusion
That concludes the discussion for today. These are some convenient methods to show TRUE if the value exists in column in Excel. Should you have questions or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.
