vlookup over multiple conditions

**Khalique Ahmed** · 10-25-2010, 03:15 AM

Dear Friends,

This is my first post in this forum but I have learnt sumproduct formula through this forum and I am very thankfull for all the seniors for their support.

Now!

I have 2 sheets 1st have list of employees with some dates (with multiple repeatition (Database) another have employees name only one time (Final Report)

like

Sheet1
A B C

Maluk Muhammad 1/09/2009 28/02/2010
Barakat Al Azmi 1/07/2009 30/06/2010
Tanveer Alam 1/11/2009 30/04/2010
Barakat Al Azmi 1/07/2010 31/12/2010
Maluk Muhammad 1/03/2010 31/08/2010

Sheet2

A

Maluk Muhammad `=VLOOKUP(A1,Final!A7:C62,MAX(2),0)
Barakat Al Azmi
Tanveer Alam
Barakat Al Azmi
Maluk Muhammad

actually I need highest date of any employee from sheet 1, but i am getting highest date of the column, means vlookup condition not working only max() working.

need instant support & help

thanks

**DonkeyOte** · 10-25-2010, 03:19 AM

Post a sample file... it sounds very much as though you should be using a Pivot Table * [rather than using Arrays etc...]

* Configure PT such that names field is a Row Label and Date Fields are Data Fields set to MAX [rather than SUM] - formatted as Dates ... see link in sig. for general Pivot intro.

**Khalique Ahmed** · 10-25-2010, 03:47 AM

Originally Posted by DonkeyOte

Post a sample file...

Please find the attached file

**DonkeyOte** · 10-25-2010, 04:53 AM

Originally Posted by Khalique Ahmed

I need highest date of any employee from sheet 1

I am a little confused by your sample file in conjunction with the above.

Your sample file results imply

"To" should be the MIN date value in Sheet1 Column B for the name specified
"From" date should be the date stored in the cell adjacent to that in which the MIN is located - rather than being the MAX of Column C for that name.

eg

Maluk Mumhammad returns 1/9/2009 and 28/2/2010 as opposed to 1/9/2009 and 31/08/2011

Can you confirm ?

**Khalique Ahmed** · 10-25-2010, 05:45 AM

"To" should be the MAX date value in sheet1 column B for the name specified
"From" should be the MAX date of Column C for the specified.
for Maluk Muhammad my result is 01/09/2009 and 28/02/2010
that is wrong it should be 01/09/2010 31/08/2011

**DonkeyOte** · 10-25-2010, 05:56 AM

In which case you should definitely be using a Pivot Table.

The Pivot in the example is sourced from a Dynamic Named Range called _PTData which will expand/contract as you add data to Sheet1 - all you need do when you revise the data is Refresh the Pivot*
*if nec. you can use VBA to automate this

**Khalique Ahmed** · 10-25-2010, 06:19 AM

Thanks for your support, your solution is perfect but another problem is that i m not much familiar with pivot table chart then first I have to learn the pivot table chart then I will be able to complete my report.

Thanks for your support.

I have joined this forum yesterday but I am feeling that why I didnt joint this forum before, it is a really very helpful plateform.

**DonkeyOte** · 10-25-2010, 06:24 AM

Regards introduction to Pivots - see the link in my signature to Jon Peltier's site.

On an aside please do not quote prior posts in their entirety - they simply clutter up your thread and the board in general.