If a value is found in a range, then return that value (not "TRUE" or it's cell reference)

Copper Contributor

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 long

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)