Hi Lucy

I think you may have your expression about column G in the wrong place -
it is within the Large function and it should be outside.

Try
{=INDEX(DATA!$B$2:$B$1000,MATCH((DATA!$G$2:$G$1000 =$A108)*
LARGE(DATA!$Z$2:$Z$1000,1),DATA!$Z$2:$Z$1000,FALSE ))}



--
Regards

Roger Govier


"LucyRB" wrote in message
...
I have two array formulas that are exactly the same, but the second
contains
an additional limiting criteria: DATA!$G$2:$G$1000=$A108. Both
formulas
worked fine for weeks, then the second suddenly started returning a
#VALUE!
error without explanation (or change to data).

I think the problem must relate to the limiting criteria works, but
don't
know how to fix it. And don't know why it worked and now doesn't.

Formula 1 (works fine):
{=
INDEX(DATA!$B$2:$B$1000,MATCH(LARGE(DATA!$Z$2:$Z$1 000,1),DATA!$Z$2:$Z$1000,FALSE))}

Formula 2 (returning #VALUE! error):
{=INDEX(DATA!$B$2:$B$1000,MATCH(LARGE((DATA!$G$2:$ G$1000=$A108)*DATA!$Z$2:$Z$1000,1),DATA!$Z$2:$Z$10 00,FALSE))}

Sample Data
Row Data!B Data!G Data!Z
2 200607028286 Joe 56.999886
3 200701000324 Bob 14.99981
4 200610048324 Sam 16.99982
5 200612058583 Bob 12.999813
6 200612059461 Joe 27.99984
7 200705020648 Sam 7.999807

Where $A108 equals Joe. Formula is used repetitively with the $A108
changing to $A109 (ie. Bob) or $A110 (ie. Sam), etc. The values in
Data!Z are
unique values to use for the LARGE calculation (generated from:
"=IF($Y3=0,0,$Y3-ROW()/1000000)").

You've helped me before and I hope you can help me again!
Thanks! Lucy