Lookup a value with multiple conditions

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.

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

Hi Ali

Not sure why it got changed. I have updated again in settings.

Hi Carcalla

I have posted question from my office laptop and we will not be able to upload any documents. Sorry for the inconvenience.

Now i logged in from my personal laptop and attached the sample sheet for your reference. Please let me know if you have any confusion.

Hello Glenn

Sorry it didn't worked out.

My customer report will not get change and i cannot split it. The only quicker solution is to look C2 cell for both EMP ID OR Emp email id and get the values in NEED FORMULA cells B2 to E2.

Is it possible to write any VBA code ?

If it is a tedious one its ok.

Thank you Glenn once again for sharing the formula appreciate all your valuable efforts.

Hello Glenn

Sorry it didn't worked out.

My customer report will not get change and i cannot split it. The only quicker solution is to look C2 cell for both EMP ID OR Emp email id and get the values in NEED FORMULA cells B2 to E2.

Is it possible to write any VBA code ?

If it is a tedious one its ok.

Thank you Glenn once again for sharing the formula appreciate all your valuable efforts.