There are multiple ways of using Excel to check if a value exists in another column such as manually using the ribbon, using a MATCH formula or using a VLOOKUP formula.
Additionally there are clever things we can do to handle dynamically expanding datasets such as by defining tables and complex ranges. But if you don’t want to know the ins and outs, and just need a quick and dirty formula to return ‘true’ or ‘false’, you can use this.
Using Excel to Check if a Value Exists in Another Column
Here we are simply checking if the value in cell A2 exists in any cell in column B (except the first row, which we assume is a header).
=IF(ISERROR(VLOOKUP(A2,$B$2:$B$1001,1,FALSE)),FALSE,TRUE)Please note that this formula has a couple of assumptions:
- It excludes the first row of data from the lookup, and assumes that this row is a header. If your data does not have headers, change it to:
=IF(ISERROR(VLOOKUP(A1,$B$1:$B$1000,1,FALSE)),FALSE,TRUE) - It also assumes your lookup data only has 1000 rows (of course it would need to iterate through 1001 rows if it contained a header!). If your dataset has more rows of data, you would also need to adjust this value.
