MS Excel

VasuSidhu
VasuSidhu used Ask the Experts™
on
I Have 4 columns viz. Name, DOB, Class & EmpID.

the first 3 columns have same entry and the last column has different entry.

Is there any formula in excel to get the answer of the 1st or second entry as for last entry lookup will work.

Name      DOB      Class      EmpID
MAX      20-12-1969      3      2
MAX      20-12-1969      3      6
MAX      20-12-1969      3      4


so that if want to get entry for 2nd set, it should come as 6.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
no

Commented:
can you give me output example i mean what you expect as a out put
Top Expert 2010
Commented:
If what you mean is, "give me the maximum EmpID for given values of Name, OB, and Class", then here is one way:

{=MAX(IF((A:A="MAX")*(B:B=DATEVALUE("12/20/1969"))*(C:C=3),D:D,""))}

To enter an array formula, do not enter the curly braces, and hit Ctrl+Shift+Enter instead of Enter to finish it off.  Excel will then display those braces to indicate that it's an array formula.

Another way would be to create a PivotTable with Name, OB, and Class as row fields, and max of EmpID as the data field.  For more about PTs please see:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3172-How-to-Automatically-Update-Your-PivotTables-and-PivotCharts.html

Author

Commented:
Thank You All. I got the answer.

=SUMPRODUCT((($D$10:$D$12=D10)*($E$10:$E$12=E10)*($F$10:$F$12=F10)*($G$10:$G$12=LARGE($G$10:$G$12,2)))*$G$10:$G$12)

Name           DOB                           Clas          EmpID
MAX           20-12-1969           3      2
MAX           20-12-1969           3      6
MAX           20-12-1969           3      4


The Data is placed in range(d9:g12)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial