Compare Two Columns Without Exact Match

[aimeemorin]

I'm trying to compare two columns in excel where there are not exact matches. For example, column A might say "Mountain Forest" but column B might say "Mountain Forest CU" so those would be a match even though they are not exact. Is this possible?

[#2]

@aimeemorin 

=INDEX($B$4:$B$7,MATCH("*"&E4&"*",$A$4:$A$7,0))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

 

[#3]

Hmmm....its ALMOST working but only returning one match. If it helps the Reference table has 167 rows and the one I need to compare it to has 241. Do I need the ABCD piece or was that just to show that the formula worked for you?

Also, when I use the formula, the red piece is referencing cell E4 which has nothing in it?