Word within a Column

[austinandreikurt]

Hi, I would like to have a search formula (not vba or query) that searches a word or string of words not only within a cell but within a range of cells or columns. Like I have the word "apple" in A1, I want it to be search if there's a matching search value in Column D and give me the row number as a result. Say it is in D88, then the formula result will be 88. I need it to get the closest match of names due to misspelling or incomplete names. If you have other suggestion, then it is very welcome. But I only need a formula or CSE formula. Thanks in advance!

 

[DanteAmor]

Something lie this:

varios 17sep2020.xlsm
	A	B	C	D	E
1	apple
2	5
3
4
5				red apple
6
7
Sheet2

Cell Formulas
Range	Formula
A2	A2	=MATCH("*"&A1&"*",D:D,0)

 

[austinandreikurt]

Ah yes, that works! I also noticed that the reason why my searches were not working properly is due to non-printable characters which are not either CHAR(160) and CHAR(32). Now I put the TRIM(CLEAN()) formula inside the MATCH. Thanks!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.