Tjyylli

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.

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

Not presenting this as a solution, just a note on troubleshooting INDIRECT() when it involves Named Ranges.

It can be exquisitely perticular. Say you have two Named Ranges" able and bravo . They refer to cells in your spreadsheet and those cells contain: 5 and able respectively.

If you use =INDIRECT(able) you get "#REF!" and for =INDIRECT(bravo) you get "able". INDIRECT() is looking at the value that has no double quotes around it as a Named Range directly snf obtaining the NR's value ("5" and "able") then resolving what it gets as if they were NR's. For "able" that's 5 and 5 isn't anything it sees as a cell address while "bravo" yields able which it finds to be a NR and returns its result.

Now put double quotes around those. You get the literal values of those cells, 5 and able. But nothing past that is done with them because the process is see the double quotes and interpret them as clean text, able and bravo, which it recognizes as NR's and obtains their values. Period. Without the quotes, you get that one more layer. Not with them though.

Why's that matter? Because the interim results Excel produces as it parses your formula and works its way, largely left to right, through solving it. Some of the individual step results have double quotes around them... and while that does not always matter, it seems "mattering" is more up to the function the string of material is passed on into than general Excel. So that kind of result might work fine in one function yet blow up in another.

INDIRECT() is VERY freaking particular and you might want to explore the EXACT form some of the interim material takes. The formula evaluator can be helpful as well as using F9 on pieces of the formula. Oddly, doing the F9 thing on a, say, 3 function wrap, might give a good result, but do it on the inner one, works great, the middle one, seems to be good still, but now doing the outer one shows an error instead of working great like it did when you F9'ed the three of them all at once. INDIRECT() is soooo much more particular than most functions and this is a lot of where failures occur for it.

(In your case, you formula is pretty involved and would seem to offer LOTS of this kind of possibility.)