Excel formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-1812757%22%20slang%3D%22en-US%22%3EExcel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1812757%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20any%20one%20suggest%20a%20formula%20for%20scenario%20below%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20column%20for%20employee's%20first%2C%20last%20name%2Fcolumn%20for%20training%20names%20they've%20completed%2Fcolumn%20with%20date%20it%20was%20completed.%20I%20need%20a%20formula%20that%20would%20look%20up%20employee%20last%20name%2Fmatch%20it%20to%20training%20that%20was%20completed%20and%20return%20training%20date%20was%20completed.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1812757%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1812777%22%20slang%3D%22de-DE%22%3ESubject%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1812777%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F843742%22%20target%3D%22_blank%22%3E%40Sofia_Nelson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20your%20permission%2C%20if%20I%20can%20recommend%20you%2C%20add%20a%20file%20(without%20sensitive%20data)%20to%20your%20project.%3C%2FP%3E%3CP%3EExplain%20your%20plans%20in%20relation%20to%20this%20file.%20So%20you%20can%20get%20a%20solution%20that%20is%20tailored%20to%20your%20needs%20much%20faster.%3C%2FP%3E%3CP%3EAt%20the%20same%20time%2C%20it%20is%20much%20easier%20for%20someone%20who%20wants%20to%20help%20understand%20the%20subject.%3C%2FP%3E%3CP%3EA%20win-win%20situation%20for%20everyone.%3C%2FP%3E%3CP%3EPlease%20no%20Picture%2C%20even%20if%20it%20is%20said%20that%20a%20picture%20can%20say%20a%20thousand%20words%2C%20it%20is%20certainly%20not%20in%20the%20case%20of%20Excel%2C%20on%20the%20contrary%20in%20some%20cases.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Knowing%20the%20Excel%20version%20and%20operating%20system%20would%20be%20an%20advantage.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1812875%22%20slang%3D%22en-US%22%3EBetreff%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1812875%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%20thank%20you%20for%20tip%2C%20I've%20added%20example%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi,

 

Can any one suggest a formula for scenario below?

 

I have column for employee's first, last name/column for training names they've completed/column with date it was completed. I need a formula that would look up employee last name/match it to training that was completed and return training date was completed.

6 Replies
Highlighted

@Sofia_Nelson 

With your permission, if I can recommend you, add a file (without sensitive data) to your project.

Explain your plans in relation to this file. So you can get a solution that is tailored to your needs much faster.

At the same time, it is much easier for someone who wants to help to understand the subject.

A win-win situation for everyone.

Please no Picture, even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases.

 

* Knowing the Excel version and operating system would also be an advantage.

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

Highlighted

@Nikolino  thank you for tip, I've added example file

Highlighted
sry but i dont see any file (with out sensitive data). Wish you a nice day. Nikolino I know I don't know anything (Socrates)
Highlighted

@Sofia_Nelson 

The formula could be

=IFNA(INDEX(Table2[[Date of Certification:]:[Date of Certification:]],
   MATCH(1,
    INDEX( ( Table4[@[Employee First Name]:[Employee First Name]]  & " " & Table4[ @[Employee Last Name]:[Employee Last Name] ]=
                   Table2[[Employee who is being certified:]:[Employee who is being certified:]])*
                   (D$1=Table2[[Choose Training Module for Certification:]:[Choose Training Module for Certification:]]),0
    ),
  0),
),"no certification")

We use two criteria to find the date: FirstName & " " & LastName; and Module name in first row. Internal INDEX() returns array of 1 or 0 if criteria met or not. We MATCH() it on1. 

Syntax like [[name]:[name]] is to fix references, similar to absolute references.

Highlighted

@Sergei Baklan  thank you soo much!!!

Highlighted

@Sofia_Nelson , you are welcome, glad to help