AND Nested in MIN Array Formula

[Tim Kelsch - ExcelForums.com]

To make this easier to understand, I'm working with a database o
automotive brake parts in which I'm trying to build a formula tha
will compose a product description from the part number, make, model
and year information that's already in the database. Part of tha
description requires the range of years, make, and model that a give
part number will work on

I have an array formula that will successfully choose the earlies
year of the various makes and models of vehicle (years are in on
column; make and model are their own separate columns) that a give
part number (in another column) will fit. My formula looks lik
this

=MIN(IF($A$3:$A$1125=A5,$E$3:$E$1125))&"-"&MAX(IF($A$3:$A$1125=A5,$E$3:$E$1125)

It will be easier to just concentrate on
=MIN(IF($A$3:$A$1125=A5,$E$3:$E$1125)
for now, as I can figure the rest out

Column A is part number, C is make, D is model, and E is year

I thought this would do the trick, but there are part numbers that fi
more than one model

I then realize that I need the first and last years that one par
number works on for EACH model of the multiple models that the par
number may fit.

In other words, one part number may fit both a 2000-2004 Ford Mustang
and a 1996-2000 Mercury Cougar. The formula that generates the yea
range for the part number only will give the year range of 1996-200
for both the Cougar and Mustang

So, I need to add another qualifier to my formula. I need to restric
the minimum year that the array formula generates to include th
minimum value in column E that has BOTH a given part number AN
model

I've tried using an AND statement, but I must be doing i
incorrectly

=MIN(IF(AND($A$3:$A$1125=A3,EXACT(D3,$D$3:$D$1125)),$E$3:$E$1125)

I also tried

=MIN(IF(AND($A$3:$A$1125=A3,$D$3:$D$1125=D3)),$E$3:$E$1125)

But neither worked. I am pressing CTRL + SHIFT + ENTER when I'm don
for reference. Any help would be greatly appreciated. :

Thanks
Tim Kelsc

 

[Biff]

Hi!

Can you post some sample data and explain what result you
expect?

Biff

 

[Frank Kabel]

Hi
try:
=MIN(IF(($A$3:$A$1125=A3)*(EXACT(D3,$D$3:$D$1125)),$E$3:$E$1125))