VLOOKUP, IF statements, and contains functions?

[AK_Excel_13]

I am trying to do something, but I am not sure if excel can do it.

In column C:C, I have a list of over 100,000 hyperlinks. These hyperlinks are very long and have a lot characters in them. These hyperlinks sometimes contains a project number.
In range F2:F100, I have a list of project numbers.

In column D:D, I would to see which hyperlinks (in column C:C) contain a project number (range F2:F100) somewhere in the text.

Can excel do this?

Thank you!

 

[ExcelGuyDude]

Yes, but you'll have to use an array formula

Excel 2010
	C	D	E	F
1	HyperLinks	Project (If Applicable)		Projects List
2	FHMK-IF0105A	F0105		F0101
3	FHMK-IF0101A	F0101		F0105
4	FHMK-IF0105A	F0105
5	FHMK-IF0101A	F0101
6	FHMK-IF0105A	F0105
7	FHMK-IF0105A	F0105
8	Fjlasdf
Sheet2

Cell Formulas
Range	Formula
D2	{=IFERROR(INDEX($F$2:$F$100,MATCH(TRUE,ISNUMBER(SEARCH($F$2:$F$100,C2)),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Check if a cell contains text compared to a range of cells | PC Review

 

[AK_Excel_13]

That furnula does not seem to be working. Maybe I have the wrong data type or wrong format cells?

 

[ExcelGuyDude]

The actual formula is this:

Cell[D2]=IFERROR(INDEX($F$2:$F$100,MATCH(TRUE,ISNUMBER(SEARCH($F$2:$F$100,C2)),0)),"")

Just make sure you're using the Ctrl+Shift+Enter. It should be able to work fine.

If you're not familiar with the Array Method, look at this website:

Excel Array Formulas - Easy Excel Tutorial

 

[AK_Excel_13]

=IFERROR(INDEX($F$2:$F$100,MATCH(TRUE,ISNUMBER(SEARCH($F$2:$F$100,C2)),0)),"")
I've the above is cell D2 and it did not work.

=IFERROR(INDEX($F$2:$F$100,MATCH(TRUE,ISNUMBER(SEARCH($F$2:$F$100,C3)),0)),"")
I've also put the above in cell D3 and it also did not work.


I do not know what I am doing wrong.

 

[ExcelGuyDude]

Are you using the Array Method?
Also, confirm that when the formula is getting ran, it is pulling the text from C2. You can walk through what the formula is doing by having the cell activated, looking in the "Formulas" tab, and using the "Evaluate Formula" feature. If it's not grabbing the correct value from cell C2, you can replace "C2" with "TEXT(C2,0)"

 

[AK_Excel_13]

I was not using the Array Method.
I tried it, and it work.

Thank you so much for your help! I really apprentice it.