Highlighted
New Contributor

# Excel formula help

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

# Betreff: Excel formula help

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

# Betreff: Excel formula help

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

Highlighted

# RE: Excel formula help

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

# Betreff: Excel formula help

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

# Betreff: Excel formula help

@Sergei Baklan  thank you soo much!!!

Highlighted

# Betreff: Excel formula help

@Sofia_Nelson , you are welcome, glad to help