EXCEL DATA MATCHING AND EXTRACTION

[Marvin Oco]

Hi All,

checking if this is possible:

1. IF RESULT IN COLUMN C IS "MATCH", CHECK ON WHICH CELL IN COLUMN D DID THE EMAIL IN COLUMN A MATCHED IN MAIL ALIAS IN COLUMN D

2. IF THERE IS DATA IN COLUMN E, EXTRACT THE CORRESPONDING USER@CONTOSO.ONMICROSOFT.COM ALIAS IN COLUMN D

please see attached excel file, sample target output in row 8

thanks

[#2]

@Marvin Oco This could be done with Power Query without much trouble. But you can achieve something similar without it, if you separate the new mailboxes from the existing ones and plit the information that you now have in column D into three separate ones. Use Text-to-columns with the colon as the delimiter. Put everything in structured Excel tables and use formulae to find matching records and extract the corresponding alias. Now, column G holds a formula that extracts the mailbox from what is in column D, stripping (SUBSTITUTE) the "SIP:" portion.

Then, in column B, a formula finds the matching row number (MATCH) in the blue table and extracts the alias from column E, stripping the "smtp:" portion.