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 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.
3 Replies
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)
