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