Forum Discussion
Cell to Range Compare with Text String Output
Hi, request for help please.
I need a formula that will compare the entry in a cell (A1) to the entries in a defined range (A2:A101), and if there is a match with any cell in the defined range provide a text string, otherwise an alternate text string.
Thanks in advance.
=IF(SUM(N(ISNUMBER(SEARCH(A1,A2:A6))))>0,"yes","no")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
6 Replies
If you want to know if any of the cells A2:A101 is equal to A1:
=IF(ISNUMBER(MATCH(A1, A2:A101, 0)), "Some Text", "Other Text")
If you want to know if any of the cells A2:A101 contains the value of A1, possibly together with other text:
=IF(ISNUMBER(MATCH("*"&A1&"*", A2:A101, 0)), "Some Text", "Other Text")
- Hans, that works well. Thank you. Unfortunately following further testing QPs answer did not work 😕
My reply is still marked as best response although the formula didn't work in your further testing. Can you give an example for which the formula doesn't work? Maybe i didn't correctly understand what you want to do.
=IF(SUM(N(ISNUMBER(SEARCH(A1,A2:A6))))>0,"yes","no")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
- That works great. Thank you QP!
