Really depends on how complicated this can get but for simple string matching something like this works.
=IFNA(MATCH(B2, $A$2:$A$9, 0), 0) >0
The above formula attempts to match a cell B2
with a range (A2-A9
). Note that it is using absolute references here so you can copy and paste the formula and not have Excel fix up the range. It uses exact matching here, FYI. If a match is found then you get the 1-based index. If it doesn't then you get N/A so the IFNA
function converts that to 0. The end result is the index of the value or 0 otherwise. So you simply compare to 0 to determine if the value existed or not.
If you're using a version of Excel > 2019 then there are newer functions that perform better. Personally I would recommend you consider moving the first column to either a named lookup table or a separate sheet. Then have your formula use the named lookup table or separate sheet instead. In the case of a named lookup it tends to be easier to search however it is harder to manage the lookup table.
If you're using Excel > 2019 then you might also look into the XLOOKUP function but that may be more than you need.