Formula to Find column1 value in column2 and highlight it

[Mahesh_B]

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

[#2]

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

[#3]

@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

[#4]

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

[#5]

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

[#6 · Accepted 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.

[#7]

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