INDEX/MATCH/LARGE returning #VALUE! error
 

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

INDEX/MATCH/LARGE returning #VALUE! error
 

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

INDEX/MATCH/LARGE returning #VALUE! error
 

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

INDEX/MATCH/LARGE returning #VALUE! error
 

Try this array formula:

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

Biff

"LucyRB" wrote in message
...
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).

INDEX/MATCH/LARGE returning #VALUE! error
 

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.

"T. Valko" wrote:

Try this array formula:

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

Biff

"LucyRB" wrote in message
...
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).

INDEX/MATCH/LARGE returning #VALUE! error
 

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

INDEX/MATCH/LARGE returning #VALUE! error
 

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

INDEX/MATCH/LARGE returning #VALUE! error
 

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

"Toppers" wrote in message
...
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.

"T. Valko" wrote:

Try this array formula:

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

Biff

"LucyRB" wrote in message
...
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).

INDEX/MATCH/LARGE returning #VALUE! error
 

Hi Biff

Quite right:
Sloppy testing: Apologies to all

--
Regards

Roger Govier


"T. Valko" wrote in message
...
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

"Toppers" wrote in message
...
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.

"T. Valko" wrote:

Try this array formula:

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

Biff

"LucyRB" wrote in message
...
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).

INDEX/MATCH/LARGE returning #VALUE! error
 

I'm just toooooo anal! <bg

Biff

"Roger Govier" wrote in message
...
Hi Biff

Quite right:
Sloppy testing: Apologies to all

--
Regards

Roger Govier


"T. Valko" wrote in message
...
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

"Toppers" wrote in message
...
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.

"T. Valko" wrote:

Try this array formula:

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

Biff

"LucyRB" wrote in message
...
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).