Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-328263%22%20slang%3D%22en-US%22%3EIf%20a%20value%20is%20found%20in%20a%20range%2C%20then%20return%20that%20value%20(not%20%22TRUE%22%20or%20it's%20cell%20reference)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-328263%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20range%20of%20cells%20(O2%3AV2).%3C%2FP%3E%3CP%3EI%20have%20a%20value%20in%20a%20single%20cell%20(B2).%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20search%20this%20range%20to%20see%20if%20any%20of%20the%20cells%20(O2%3AV2)%20match%20B2.%3C%2FP%3E%3CP%3EIf%20any%20of%20the%20cells%20in%20the%20range%20match%2C%20then%20I%20need%20it%20to%20return%20the%20match%20value%20(not%20a%20value%20of%20%22TRUE%22%20but%20the%20actual%20value).%3C%2FP%3E%3CP%3EIt%20is%20unknown%20where%20within%20the%20range%20the%20matched%20cell%20exists%2C%20so%20I%20cannot%20use%20a%20reference%20cell.%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20do%20this%3F%3C%2FP%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CP%3EB2%3DApple%3C%2FP%3E%3CP%3EO2%3DOrange%3C%2FP%3E%3CP%3EP2%3DPear%3C%2FP%3E%3CP%3EQ2%3DLemon%3C%2FP%3E%3CP%3ER2%3DApple%3C%2FP%3E%3CP%3ES2%3DLime%3C%2FP%3E%3CP%3EI%20need%20the%20formula%20in%20C2%20to%20display%20%22Apple%22%20from%20R2.%3C%2FP%3E%3CP%3EThe%20next%20row%20could%20be%20different.%20For%20example%3A%3C%2FP%3E%3CP%3EB3%3DApple%3C%2FP%3E%3CP%3EO3%3DLemon%3C%2FP%3E%3CP%3EP3%3DApple...%3C%2FP%3E%3CP%3EIn%20this%20row%2C%20I%20need%20to%20apply%20the%20same%20formula%20as%20in%20Row%202%20to%20search%20this%20range%20and%20return%20%22Apple%22%20no%20matter%20where%20it%20is%20found%20in%20the%20Row%203%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-328263%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-328744%22%20slang%3D%22en-US%22%3ERe%3A%20If%20a%20value%20is%20found%20in%20a%20range%2C%20then%20return%20that%20value%20(not%20%22TRUE%22%20or%20it's%20cell%20refere%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-328744%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DINDEX(2%3A2%2CAGGREGATE(15%2C6%2CCOLUMN(O2%3AV2)%2FISNUMBER(SEARCH(B2%2CO2%3AV2))%2C1))%3CBR%20%2F%3E%3CBR%20%2F%3E%3DLOOKUP(9%5E9%2CSEARCH(B2%2CO2%3AV2)%2CO2%3AV2)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-328706%22%20slang%3D%22en-US%22%3ERe%3A%20If%20a%20value%20is%20found%20in%20a%20range%2C%20then%20return%20that%20value%20(not%20%22TRUE%22%20or%20it's%20cell%20refere%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-328706%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%2C%20I%20should%20have%20been%20more%20specific...%3C%2FP%3E%3CPRE%3E%3DIF(COUNTIF(O2%3AV2%2CB%242)%2CB%242%2C%22no%20match%22)%20would%20bring%20back%20%22APPLE%22%20if%20it%20was%20an%20exact%20match.%20I%20am%20looking%20for%20a%20cell%20that%20may%20contain%20%22RED%20APPLE%22%20or%20%22GREEN%20APPLE%22%2C%20and%20then%20bring%20back%20either%20%22RED%20APPLE%22%20or%20%22GREEN%20APPLE%22%20(whichever%20one%20it%20found).%3CBR%20%2F%3EA%20work%20around%20I%20had%20to%20create%20was%20this%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3Eif%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3Eisnumber%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3Esearch%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20string%20%22%3E%22*%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%26amp%3B%3C%2FSPAN%3E%3CSPAN%3EE2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%3EP2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%3EP2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3Eif%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3Eisnumber%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3Esearch%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20string%20%22%3E%22*%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%26amp%3B%3C%2FSPAN%3E%3CSPAN%3EE2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%3EQ2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%3EQ2%3C%2FSPAN%3E%3CBR%20%2F%3EBut%20I%20needed%20to%20keep%20this%20formula%20going%20for%20every%20column%20I%20wanted%20it%20to%20search%20(there%20are%2020%20columns)%2C%20so%20this%20formula%20got%20VERY%20long%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-328339%22%20slang%3D%22en-US%22%3ERe%3A%20If%20a%20value%20is%20found%20in%20a%20range%2C%20then%20return%20that%20value%20(not%20%22TRUE%22%20or%20it's%20cell%20refere%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-328339%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUntested.%3C%2FP%3E%3CPRE%3E%3DIF(COUNTIF(O2%3AV2%2CB%242)%2CB%242%2C%22no%20match%22)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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
Highlighted

Hello

 

Untested.

=IF(COUNTIF(O2:V2,B$2),B$2,"no match")

 

Highlighted

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

Highlighted

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)