SOLVED

Formula to Find column1 value in column2 and highlight it

%3CLINGO-SUB%20id%3D%22lingo-sub-2386947%22%20slang%3D%22en-US%22%3EFormula%20to%20Find%20column1%20value%20in%20column2%20and%20highlight%20it%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2386947%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20data%20like%20below.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22150pt%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2277px%22%20height%3D%2220%22%3EColumn%201%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3Ecolumn2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277px%22%20height%3D%2220%22%3E11%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3ERTG-2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277px%22%20height%3D%2220%22%3E12%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3EGDR-4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277px%22%20height%3D%2220%22%3E13%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3ELAP-6%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277px%22%20height%3D%2220%22%3E14%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3EMAT-8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277px%22%20height%3D%2220%22%3E15%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3EOPR-0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277px%22%20height%3D%2220%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E%3CFONT%20color%3D%22%23FFCC00%22%3EAMG-11p%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277px%22%20height%3D%2220%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E%3CFONT%20color%3D%22%23FFCC00%22%3EXTR-12%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277px%22%20height%3D%2220%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E%3CFONT%20color%3D%22%23FFCC00%22%3EYHE-13H%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277px%22%20height%3D%2220%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E%3CFONT%20color%3D%22%23FFCC00%22%3EOVT-14%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277px%22%20height%3D%2220%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E%3CFONT%20color%3D%22%23FFCC00%22%3EKHK-15%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277px%22%20height%3D%2220%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3EUTH-16%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277px%22%20height%3D%2220%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3EIKL-17%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20want%20to%20find%20value%20of%20column%20in%20column%202%20.%20Column%202%20will%20not%20have%20exact%20value%2C%20but%20will%20have%20complete%20value%20present%20.%3CBR%20%2F%3EAnd%20once%20I%20find%20value%20I%20want%20to%20highlight%20the%20same%20cell%20with%20some%20color%20of%20put%20that%20in%20column%203.%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20any%20way%20to%20achieve%20this%20%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%2C%3CBR%20%2F%3EMahesh%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2386947%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2387064%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20Find%20column1%20value%20in%20column2%20and%20highlight%20it%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2387064%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1063604%22%20target%3D%22_blank%22%3E%40Mahesh_B%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20numbers%20are%20in%20A1%3AA5%20and%20the%20text%20values%20in%20C1%3AC12.%3C%2FP%3E%0A%3CP%3ESelect%20C1%3AC12.%20C1%20should%20be%20the%20active%20cell%20in%20the%20selection.%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20select%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format'.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DISNUMBER(MATCH(1%2CMATCH(%22*-%22%26amp%3B%24A%241%3A%24A%245%26amp%3B%22*%22%2CC1%2C0)%2C0))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3ESpecify%20the%20desired%20formatting.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2387136%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20Find%20column1%20value%20in%20column2%20and%20highlight%20it%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2387136%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1063604%22%20target%3D%22_blank%22%3E%40Mahesh_B%3C%2FA%3E%26nbsp%3Balternatives%3A%3C%2FP%3E%3CP%3Eformula%20in%20column%20C%3A%3C%2FP%3E%3CP%3E%3DLET(in%2CB2%3AB30%2Cz%2C%24A%242%3A%24A%246%2CFILTER(in%2CMMULT(IFERROR(SEARCH(TRANSPOSE(z)%2Cin)%2C0)%2CSEQUENCE(ROWS(z)%2C1%2C1%2C0))))%3C%2FP%3E%3CP%3Eor%20conditional%20formatting%20formula%20(based%20on%20range%20in%20column%20B%20starting%20at%20B1)%3A%3C%2FP%3E%3CP%3E%3DSUM(IFERROR(SEARCH(%24A%242%3A%24A%246%2CB1)%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esee%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2387322%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20Find%20column1%20value%20in%20column2%20and%20highlight%20it%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2387322%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20solution.%3C%2FP%3E%3CP%3EI%20tried%20solution%2C%20but%20somehow%20its%20not%20working.%3C%2FP%3E%3CP%3EAttaching%20file%20for%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20please%20look%20and%20help%20me%20with%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3EMahesh%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2387435%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20Find%20column1%20value%20in%20column2%20and%20highlight%20it%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2387435%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3EI%20tried%20but%20its%20not%20working.%3CBR%20%2F%3ECan%20you%20please%20help%20with%20this%3F%3CBR%20%2F%3EI%20have%20attached%20file%20here%20where%20I%20am%20trying%20to%20apply%20this%20formula.%3CBR%20%2F%3EThanks%2C%3CBR%20%2F%3EMahesh%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

I have data like below.

 

 

Column 1column2
11RTG-2
12GDR-4
13LAP-6
14MAT-8
15OPR-0
 AMG-11p
 XTR-12
 YHE-13H
 OVT-14
 KHK-15
 UTH-16
 IKL-17

 

I want to find value of column in column 2 . Column 2 will not have exact value, but will have complete value present .
And once I find value I want to highlight the same cell with some color of put that in column 3.

Is there any way to achieve this ?

Thanks,
Mahesh

6 Replies

@Mahesh_B 

Let's say the numbers are in A1:A5 and the text values in C1:C12.

Select C1:C12. C1 should be the active cell in the selection.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

 

=ISNUMBER(MATCH(1,MATCH("*-"&$A$1:$A$5&"*",C1,0),0))

 

Click Format...

Specify the desired formatting.

Click OK, then click OK again.

@Mahesh_B alternatives:

formula in column C:

=LET(in,B2:B30,z,$A$2:$A$6,FILTER(in,MMULT(IFERROR(SEARCH(TRANSPOSE(z),in),0),SEQUENCE(ROWS(z),1,1,0))))

or conditional formatting formula (based on range in column B starting at B1):

=SUM(IFERROR(SEARCH($A$2:$A$6,B1),0))

 

see attached

Hi @mtarler ,

 

Thank you for your solution.

I tried solution, but somehow its not working.

Attaching file for reference.

 

Can you please look and help me with this?

 

Thank you,

Mahesh

Hi@Hans Vogelaar ,

I tried but its not working.
Can you please help with this?
I have attached file here where I am trying to apply this formula.
Thanks,
Mahesh

best response confirmed by Mahesh_B (New Contributor)
Solution

@Mahesh_B The range in column A included a blank cell at the end and it is a dynamic array formula so you only needed to paste it in the first cell.  If you don't have Excel 365 it won't work.

I fixed both issues and added the conditional formatting option in the attached.

@Mahesh_B 

You posted a workbook with mtarler's formula.

Your data look different from your sample, so you need a slightly different formula.

See the attached version.