Forum Discussion

sbruk89's avatar
sbruk89
Copper Contributor
Jun 30, 2023

** HELP WITH INDEX & MATCH FORMULA **

Hi All,

 

Im struggling with an index and match formula and require some assistance please 🙂

 

I need to create and index and match that will return the required email based on the MDF code as seen in column A and return the email in column B.

 

e.g if the beginning of the code begins with ES then it will go to FRCT.SCOTLAND and as in the example below if it beings with EA it should go to FRCT.MIDLANDS.

 

the formula as seen in the screenshot so far is =INDEX($B$3:$B$38, MATCH(1, ($A$3:$A$38=LEFT(E12,2))))

 

Any help would be appreciated!

 

 

excel
Excel for web
Formulas and Functions
training

1 Reply

Sort By
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jun 30, 2023

    sbruk89 

    =INDEX($B$3:$B$38, MATCH(LEFT(E12,2), $A$3:$A$38,0))

    This returns the intended result in my sheet.

    =INDEX($B$3:$B$38, MATCH(TRUE, $A$3:$A$38=LEFT(E12,2),0))

    This formula works as well but it has to be entered with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021 because it's an arrayformula.

Resources