LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default 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

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
index match array function-returning only first match, need last. Julie Olsen Excel Worksheet Functions 3 December 29th 06 12:50 AM
Returning MULTIPLE values with Index and Match Fly Excel Discussion (Misc queries) 1 June 1st 06 05:50 PM
Match/Index Returning #N/A Will Excel Worksheet Functions 6 April 15th 06 05:10 AM
Match, Index, Vlookup, Large....Help Please [email protected] Excel Worksheet Functions 3 December 15th 04 01:38 PM
Match, Index, Vlookup, Large....Help Please [email protected] Excel Worksheet Functions 0 December 14th 04 11:16 PM


All times are GMT +1. The time now is 09:46 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"