Forum Discussion

Mahesh_B's avatar
Mahesh_B
Copper Contributor
May 26, 2021
Solved

Formula to Find column1 value in column2 and highlight it

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

Formulas and Functions
  • mtarler's avatar
    mtarler
    May 26, 2021

    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.

6 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    May 26, 2021

    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

    • Mahesh_B's avatar
      Mahesh_B
      Copper Contributor
      May 26, 2021

      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

      • mtarler's avatar
        mtarler
        Silver Contributor
        May 26, 2021

        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.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 26, 2021

    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.

Resources