SOLVED

New Contributor

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

# Re: Formula to Find column1 value in column2 and highlight it

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.

# Re: Formula to Find column1 value in column2 and highlight it

@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

# Re: Formula to Find column1 value in column2 and highlight it

Hi @mtarler ,

I tried solution, but somehow its not working.

Attaching file for reference.

Can you please look and help me with this?

Thank you,

Mahesh

# Re: Formula to Find column1 value in column2 and highlight it

I tried but its not working.
I have attached file here where I am trying to apply this formula.
Thanks,
Mahesh

best response confirmed by Mahesh_B (New Contributor)
Solution

# Re: Formula to Find column1 value in column2 and highlight it

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

# Re: Formula to Find column1 value in column2 and highlight it

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.