G
Guest
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$1000,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$1000,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
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$1000,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$1000,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