Jan 30 2019 02:33 PM - edited Jan 30 2019 02:33 PM
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 need it to return the match value (not a value of "TRUE" but the actual value).
It is unknown where within the range the matched cell exists, so I cannot use a reference cell.
Is there a way to do this?
For example:
B2=Apple
O2=Orange
P2=Pear
Q2=Lemon
R2=Apple
S2=Lime
I need the formula in C2 to display "Apple" from R2.
The next row could be different. For example:
B3=Apple
O3=Lemon
P3=Apple...
In this row, I need to apply the same formula as in Row 2 to search this range and return "Apple" no matter where it is found in the Row 3 range.
Jan 30 2019 08:00 PM
Hello
Untested.
=IF(COUNTIF(O2:V2,B$2),B$2,"no match")
Jan 31 2019 10:00 AM
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 long
Jan 31 2019 10:56 AM
Hi
=INDEX(2:2,AGGREGATE(15,6,COLUMN(O2:V2)/ISNUMBER(SEARCH(B2,O2:V2)),1))
=LOOKUP(9^9,SEARCH(B2,O2:V2),O2:V2)