Oct 23 2020 08:23 AM - edited Oct 23 2020 08:41 AM
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.
Oct 23 2020 08:24 AM
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)
Oct 23 2020 08:41 AM
@NikolinoDE thank you for tip, I've added example file
Oct 24 2020 02:57 AM
Oct 24 2020 01:47 PM
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.