INDEX/MATCH/LARGE returning #VALUE! error

[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

 

[Roger Govier]

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))}

 

[Guest]

Thanks for your prompt reply, Roger. I tried your suggestion, but it didn't
work. It returned the number from Data!B, row 2 (ie. the very first number
in Data!B column).

 

[T. Valko]

Try this array formula:

=INDEX(DATA!B2:B1000,MATCH(MAX((DATA!G2:G1000=A108)*DATA!Z2:Z1000),DATA!Z2:Z1000,0))

Biff

 

[Guest]

FYI: I tried your and Biff's formula and both worked fine (giving identical
answers) on the data you posted. #VALUE suggests (to me) a data problem.

 

[Guest]

Thanks to all of you for looking into this for me. I really appreciate your
time.

Based on your information, I re-tested running the report with just a few
rows of data (rather than the usual 1000 rows) and you are right: the
formula worked. I had just assumed it was a formula error. Now I have to go
and search out the error in the raw data.

Thanks again!!
Lucy

 

[Guest]

Wanted to report back. I found the data problem. One cell in Data!Z was empty
(missing a formula used to calculate LARGE). I don't know how it got deleted
(worksheet was protected!), but that was the cause of the problem. Phew!

Thanks again!!
Lucy

 

[T. Valko]

If I understand what the OP wants I think Roger's formula has a bug.

Return account number from column A that corresponds to the highest number
in column Z for the specific name in column G referenced in cell A108.

Roger's formula works for Joe but no other names. The problem is:

MATCH((DATA!$G$2:$G$1000=$A108)*LARGE(DATA!$Z$2:$Z$1000,1)

(DATA!$G$2:$G$1000=$A108)

This doesn't get processed as an array. If you have Excel 2002 or higher use
the Evaluate Formula menu command and you'll see what I mean. (I would use a
smaller dataset before you try this!)

Here's a small sample file:

array.xls 14kb

http://cjoint.com/?gbxe2M7tyU

Change the name in A10 and see what happens.

Also, based on the (limited) sample data provided you'll notice that the
numeric values are in descending order for each name. If that's how the real
data is then this simplified formula will do the same thing:

=INDEX(DATA!B2:B1000,MATCH(A108,DATA!G2:G1000,0))

Biff

 

[Roger Govier]

Hi Biff

Quite right:
Sloppy testing: Apologies to all

 

[T. Valko]

I'm just toooooo anal! <bg>

Biff

Hi Biff

Quite right:
Sloppy testing: Apologies to all