Forum Discussion
If a value is found in a range, then return that value (not "TRUE" or it's cell reference)
I have a range of cells (O2:V2). I have a value in a single cell (B2). I am trying to search this range to see if any of the cells (O2:V2) match B2. If any of the cells in the range match, then I ...
Hello
Untested.
=IF(COUNTIF(O2:V2,B$2),B$2,"no match")
Sorry, I should have been more specific...
=IF(COUNTIF(O2:V2,B$2),B$2,"no match") would bring back "APPLE" if it was an exact match. I am looking for a cell that may contain "RED APPLE" or "GREEN APPLE", and then bring back either "RED APPLE" or "GREEN APPLE" (whichever one it found).
A work around I had to create was this:
=if(isnumber(search("*"&E2,P2)),P2,if(isnumber(search("*"&E2,Q2)),Q2
But I needed to keep this formula going for every column I wanted it to search (there are 20 columns), so this formula got VERY longHi
=INDEX(2:2,AGGREGATE(15,6,COLUMN(O2:V2)/ISNUMBER(SEARCH(B2,O2:V2)),1))
=LOOKUP(9^9,SEARCH(B2,O2:V2),O2:V2)
