| Home |
| Search |
| Today's Posts |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
| Returning MULTIPLE values with Index and Match | Excel Discussion (Misc queries) | |||
| Match/Index Returning #N/A | Excel Worksheet Functions | |||
| Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
| Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
Threaded Mode
