How to put an IF function with VLOOKUP with more than one matching entry!?

[arunk8186]

Hi friends,
I am sure this could be done but I am not able to do. Need a little help.

I have an excel sheet with first "A" column filled with Employee Names (unique without duplicates). In another sheet, I have "A" column filled with the same Employee names (with duplicates) and "B" column filed with their respective marks scored in different subjects like Marketing, HR, Finance, Operations etc.

Note that the data in "B" column has only all the mark listed with no way of differentiating which subject it is scored in.

I also put a COUNTIF to get the number of mark entries each employee has.

The pass mark is 40.

I want to know which all employees passed ALL the subjects. A simple VLOOKUP and IF function wcould help but VLOOKUP will compare only the first mark entry of the corresponding employee with 40 and give the result.

But I want to put a modified VLOOKUP/MATCH function which will help compare the all marks scored by each employee in ALL subjects with 40 and tell whether s/he passed or not.

Any suggestions/comments will be very much appreciated.

Thanks and Cheers,
Arun

 

[Andrew Poulsom]

To count the number of marks greater than 40 for an employee:

=SUMPRODUCT(--(Sheet2!A1:A1000="Employee"),--(Sheet2!B1:B1000>40))

If that returns the same as your COUNTIF formula the employee passed all subjects.