2

I am creating a worksheet that compares two sets of data pulled on different dates. The column that everything pulls off of is created by an array formula comprised of combined unique values from the two sets of data. Previously, I had it working great because I was manually putting the titles of the lists (List1 and List2):

=IFERROR(IFERROR(INDEX(List1,MATCH(0,IF(ISBLANK(List1),1,COUNTIF($B$3:B3,List1)),0)),INDEX(List2,MATCH(0,IF(ISBLANK(List2),1,COUNTIF($B$3:B3,List2)),0))),"")

Now I am trying to set up the worksheet to hold multiple dates with a menu to select your dates. I set up a reference table so that each spreadsheet has it's own list based on the date it was pulled:

DATES       TAB         LIST
2/4/2015    Requireds A ListA
2/5/2015    Requireds B ListB
2/6/2015    Requireds C ListC
2/7/2015    Requireds D ListD
2/8/2015    Requireds E ListE

I have updated my original formula by replacing 'List1' with referencing the first drop down menu to find the list column in the reference table, and 'List2' to find the second date using Index and Match:

=IFERROR(IFERROR(INDEX(
(INDEX(DropDownTable!A:C,MATCH(Comparison!B1,DropDownTable!A:A,0),3))
,MATCH(0,IF(ISBLANK(
(INDEX(DropDownTable!A:C,MATCH(Comparison!B1,DropDownTable!A:A,0),3))
),1,COUNTIF($B$3:B3,
(INDEX(DropDownTable!A:C,MATCH(Comparison!B1,DropDownTable!A:A,0),3))
)),0)),INDEX(
(INDEX(DropDownTable!A:C,MATCH(Comparison!D1,DropDownTable!A:A,0),3))
,MATCH(0,IF(ISBLANK(
(INDEX(DropDownTable!A:C,MATCH(Comparison!D1,DropDownTable!A:A,0),3))
),1,COUNTIF($B$3:B3,
(INDEX(DropDownTable!A:C,MATCH(Comparison!D1,DropDownTable!A:A,0),3))
)),0))),"")

HOWEVER, here is my problem, when I run 'evaluate formula' it appears as though while the majority of my new look ups will pull the list name, one or two do not. It pulls the list name's cell. So instead of this bringing me 'ListA' it is bringing me:

DropDownTable!$C$2

I don't know if this is the only error in the formula; I'm unsure if having the words 'ListA' is the same as referencing the list itself. That may be something I am messing up, too.

Any guidance would be excellent, but I can't figure out why it doesn't just tell me 'ListA' like it should.

0

I'm not sure what you mean.

Your very first INDEX is expecting a range as the first parameter, and so the result of:

INDEX(DropDownTable!A:C,MATCH(Comparison!B1,DropDownTable!A:A,0),3)

must be such.

INDEX is a flexible enough function such that the result generated can be either an actual cell value or a cell reference. This is an extremely useful property, as you can imagine.

And this is where I don't really understand your formula. The result of the above expression will result in a single cell reference, e.g. DropDownTable!$C$8, which is then passed as the range to the first INDEX, which will become something like:

INDEX((DropDownTable!$C$8,MATCH(0,...

and so no matter what the result of the following expression, you are still never going to achieve much: INDEXing a single cell does not have much point!

Very difficult to help you further. This may actually be a case where you'd be better off not posting your existing formulas and instead simply giving a breakdown of what you're trying to achieve. It's often far easier to give solutions starting from scratch than to error-check those of others, particularly if, as in your case, they involve constructions as long and difficult-to-dissect as yours.

Regards

|improve this answer|||||
  • (Pressing enter posts the comment--you learn something new everyday!) The value of the cell being returned is the name of a range, which is the range I would like to use. What I'm trying to achieve: When a cell says a certain date, I want my formula to look that date up and use the corresponding named range from a table. My ranges are named ListA through ListE. I want that first index to use one of the named ranges as the index. The most frustrating is it works how I expect it to later in the formula, but in the beginning of the formula it does not. – elcoxita Feb 13 '15 at 20:16
  • I see. Then the cell which contains the name of that Named Range, and which you are generating via your second INDEX function, must first be passed to INDIRECT prior to being passed to the further INDEX. For example: =IFERROR(IFERROR(INDEX(INDIRECT(INDEX(DropDownTable!A:C,MATCH(Comparison!B1,DropDownTable!A:A,0),3)),MATCH(0,..., etc. – XOR LX Feb 13 '15 at 20:40
  • Adding in INDIRECT makes the step before INDIRECT is calculated return 'ListA', but then I get #ref once INDIRECT is calculated. I'm unfamiliar with how indirect works, but I'm checking into it. I understand what you're saying at least, and I may be able to find a work around if my result of the second index should be an array. – elcoxita Feb 13 '15 at 20:55
  • The entry in the cell being passed to INDIRECT must be PRECISELY the same as the Named Range which it supposedly represents. No extra spacing, hyphens, underscores, etc. in one but not the other. – XOR LX Feb 13 '15 at 21:07
  • They are named exactly the same and I'm still getting (Indirect("ListA") and then when it calculates it goes #REF.. I have made it work by using a long IF clause. I may be trying to think it out more complicated than it should be, so if you have any other ideas throw 'em at me :) – elcoxita Feb 16 '15 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.