Forum Discussion

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...

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.
Find Columns.xlsx27 KB

MVP

May 26, 2021

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.

Copper Contributor

May 26, 2021

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