Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
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 |