In this article, we will discuss receiving ‘TRUE’ as output if one cell value in Excel exists in another column. Basically, when we work with spreadsheets, it is not possible to find a particular cell value in a large array one by one. Fortunately, there are various ways in Excel that can help us to do this lookup and match the task. Besides that, we can use simple formulas or combinations of functions depending on the type and volume of the data.
Download the Practice Workbook
You can download the practice workbook that we have used to prepare this article.
5 Methods to Return TRUE If Value Exists in a Column in Excel
1. Use Simple Formula to Find TRUE If Excel Column Consists a Value
This is one of the easiest methods to match data between columns and return TRUE. So, here are the steps:
- First, type the following formula in the first cell of the result column (here, Cell D5).
- After Entering the formula, you will get TRUE as output if both column values match, otherwise FALSE. Then, use autofill (+) to drag down the formula to the rest of the column.
2. Return TRUE Using EXACT Function If a Value Exists in Excel Column
Sometimes, we want to match case-sensitive data between columns and get the matched results. In such cases, the EXACT function can be a great help. The EXACT function checks whether two text strings are exactly the same, and returns TRUE or FALSE. This function is case-sensitive. The steps we have followed for this method are:
- First, type the following formula:
- If you enter the formula correctly, the following will be the output.
3. Use Combination of MATCH, ISERROR and NOT Functions to Get TRUE If Value Exists in Excel Column
Earlier in this article, we were talking about using combinations of functions to match a particular cell value in a range of data. Interestingly, there are several combinations to do the task. For example, we can use MATCH, ISERROR, and NOT functions altogether. In the current example, we have a fruit dataset, and we will look for a particular fruit name in a column containing a list of other fruits.
- To get the expected result, type the following formula:
Breakdown of the Formula:
Here, the MATCH function returns the relative position of an item in an array that matches a specified value in a specified order.
Now, the ISERROR function checks whether a value is an error, and returns TRUE or FALSE.
Finally, the NOT function changes FALSE to TRUE or FALSE to TRUE.
- You will get the following result if the formula is entered correctly.
4. Return TRUE If a Value Present in an Excel Column Using the Combination of IF, ISERROR and VLOOKUP Functions
Likewise in the previous example, we will use another combination of functions to get the TRUE output if a particular value is available in another column. Now, we will use the combination of the IF, ISERROR and VLOOKUP functions. For instance, we want to know if any number in a cell of column B is available in column C or not. Here are the steps we will follow:
- First, type the below formula:
Breakdown of the Formula:
Here, the VLOOKUP function looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify. The function will look for the value of Cell B5 in range C5:C13.
Now, the ISERROR function checks whether a value is an error, and returns TRUE or FALSE. Finally,
The IF function checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.
- As a consequence of entering the formula, you will get the following result:
5. Use the Combination of ISNUMBER and MATCH Functions to Find TRUE If Value Remain in a Column in Excel
Similar to methods 3 and 4, now we will apply another combination of functions to search a particular cell value in a column. Such as we will combine the ISNUMBER and MATCH function to search the value and get ‘TRUE’ as output. Like, we want to find any month of column B in the month list of column C. So, the steps we have followed here are:
- To get the desired result, type the below formula at first:
Here, the MATCH function will look and match the value of Cell B5 in the range C5:C13, and the ISNUMBER function checks whether a value is a number, and returns TRUE or FALSE.
- In the end, you will get the result as follows.
In the above-mentioned article, I have tried to explain lookup and match methods elaborately. These methods are very easy and take less time. Especially, when you will work with big data, the methods mentioned above will save a lot of your time and energy. If you have any queries regarding these methods, let me know.