INDEX/MATCH/LARGE returning #VALUE! error


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
 
Ad

Advertisements

R

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

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

T. Valko

Try this array formula:

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

Biff
 
G

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.
 
G

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
 
Ad

Advertisements

G

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

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top