Min/Max Matching Between Ranges

**cbosch** · 07-11-2011, 12:52 PM

I have a worksheet that's been baffling me for some time now.

I have 2 sheets (see below)

Sheet 1:
Name | MIN Start Date | MAX End Date

Sheet 2:
Name | Start Date | End Date

Named Ranges:
_Est_End_Date_Active_Column (range of all end dates)
_Start_Date_Active_Column (range of all start dates)
_Assignment_Active_Column (range of all names)

What I want to do is match the MIN Start Date where Sheet 1 Name is in the row.

So for example:

Data:
Jason | 1/1/11 | 2/15/11
Tommy | 2/2/11 | 3/15/11
Justin | 3/3/11 | 4/15/11
Sal | 4/4/11 | 5/15/11

Sheet 1:
Justin | 1/1/11 | 2/15/11

What I am getting is:

Justin: 1/1/11 | 5/15/11

Here's the formula I've been working with:

=MAX(IF(_Assignment_Active_Column=$A14,_Est_End_Date_Active_Column))

Not sure what I am doing wrong...

**NBVC** · 07-11-2011, 01:00 PM

Are you using the right named range for start dates?

Also, are you confirming formula with CTRL+SHIFT+ENTER not just ENTER since it is an array formula?

**cbosch** · 07-11-2011, 01:07 PM

The named ranges are correct. The _Start_Date_Active_Column is a named range for all of the start dates in the Start Date Column and the _Est_End_Date_Active_Column is a named range for all of the end dates in the Est End Date Column.

I tried both 'Enter' and 'CTRL-SHIFT-ENTER' and recieved the same date back.

It seems that it's pulling the MAX and MIN dates, but it's not looking to verify that the name is associated with the dates.

What it should be pulling, for example, if I am looking for the MAX end date for "Jason" would be 2/15/11, but instead it's stating the MAX end date is 5/15/11.

**NBVC** · 07-11-2011, 01:10 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!