Hi All

Good morning . I am using Office 365 version at my work place.

I have values in a column " C " in our report which comes from customer. These are certifications earned data. This column has employees email id (sample vr.vijay@abc.com) OR their Azure Active Directory ID which is in the format of (first 2 letters of their name + their Employee ID with 8 digits) Sample: VI42675432

What i am looking for?

In column D i have to lookup the value in column C and get the Employee Name from our internal reports.
In column E i have to lookup the value in column C and get the Employee Email id from our internal reports.

So overall i need a formula in column D & E by lookup with column " C "and it should pick either Employee ID(42675432) OR Email idand get the value OR it should pic



ColumnC ColumnD
VI42675432 OR vr.vijay@abc.com If the column C value is with Azure Active Directory ID it should pick only number part OR if it is with email id it should pick email id and get the value of Emp id from our internal report.