excel formula - Can you match or search with cell matching part of another?

Question

I'm trying to create a formula in Excel where I can use an Index / Match formula where I'm searching for only part of one cell.

For example:

Sheet 1

Column A --> Full Name

Column B --> Removed status

	Col A	Col B	Col C	Col D	Col E
1	Col D + Col E	Removed status	agent #	Agent F Name	Agent L Name
2

question from:https://stackoverflow.com/questions/65907865/can-you-match-or-search-with-cell-matching-part-of-another

Answer 1

If you want to search for part of one cell, you can use find formula which search for given text within cell value find(text,withintext) and it return index that the text start with and error if not.

And if you need your process to be work line by line, the final formula ( for row2 of sheet2 column A ) would look like this:

=IF(AND(NOT(ISERROR(FIND(Sheet1!A2,Sheet2!D2))),Sheet1!A2<>""),Sheet1!A2,"")

if row2 of column A of sheet1 is not empty and is found (no error returned);the value of sheet1 column A returned otherwise empty will returned.

Hope that was useful.