Return Cell Address of MIN value - array formula
The "easy" way is to use your current formula and then refer to that
formula:
Assume your current formula is in A1.
Then, this array formula:
="J"&MATCH(1,(LEFT(G3:G10,3)="BJ ")*(H3:H10=L9)*(J3:J10=A1),0)+2
If you want it all in a single formula then in the above formula replace
this:
(J3:J10=A1)
with your current formula. By the time you add the sheet names it's gonna be
a real mess!
--
Biff
Microsoft Excel MVP
wrote in message
ups.com...
Hey
The following formula returns a numeric(percent) value:
=MIN(IF((LEFT('PDAs Report'!G3:G1000,3)="BJ ")*('PDAs Report'!
H3:H1000=L9),'PDAs Report'!J3:J1000))
In order for the numeric values to even be evaluated, the following 2
conditions must be met:
The value in range G3:G1000 must contain "BJ ".
The numeric value in range H3:H1000 MUST be greater than the numeric
value in cell L9.
The formula then returns the minimum value from all the values that
met the above criteria.
My question is how can I make it return the actual cell address
instead of the value of the cell?
Can you please provide me a few methods to reach the desired result?
Thanks
Todd
|