"Harlan Grove" wrote in message
ps.com...
wrote...
...
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))
...
My question is how can I make it return the actual cell address
instead of the value of the cell?
...

Try the array formula

=SUBSTITUTE(CELL("Address",INDEX('PDAs Report'!J3:J1000,
MATCH(MIN(IF((LEFT('PDAs Report'!G3:G1000,3)="BJ ")
*('PDAs Report'!H3:H1000=L9),'PDAs Report'!J3:J1000)),
'PDAs Report'!J3:J1000,0))),"$","")

If you don't want the workbook/worksheet name included, try the array
formula

=ADDRESS(MATCH(MIN(IF((LEFT('PDAs Report'!G3:G1000,3)="BJ ")
*('PDAs Report'!H3:H1000=C1),'PDAs Report'!J3:J1000)),
'PDAs Report'!J3:J1000,0)+2,10,4)

Note that this is specific to your 'PDAs Report'!J3:J1000 range (the
+2 adjusts the MATCH result to the correct row number, and 10 is the
column index of column J). The last argument, 4, specifies fully
relative cell address.


I didn't try the first formula but the second formula doesn't account for
duplicate MIN's not meeting the conditions.

Surely you tested for that? <g

--
Biff
Microsoft Excel MVP