Home

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

sronalds
New 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)
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies