Forum Discussion

aimeemorin's avatar
aimeemorin
Copper Contributor
Sep 12, 2022

Compare Two Columns Without Exact Match

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?

excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Sep 12, 2022

    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.

     

    • aimeemorin's avatar
      aimeemorin
      Copper Contributor
      Sep 13, 2022

      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?

Resources