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 1 column2 11 RTG-2 12 GDR-4 13 LAP-6 14 MAT-8 15 OPR-0   AMG-11p   XTR-12   YHE-13H   OVT-14   KHK-15   UT...
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.

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.

Resources