0

In Excel 2010, I have a formula that works like this:

=OFFSET(OFFSET(Table!$E$8,0,0,1,1),MATCH(Criteria1!$C9&Criteria2!$D9,Plan[Wirecentre]&Plan[Type],0)-1,0,COUNTIFS(Table[Criteria1Range],Criteria1!$C9,Table[Criteria2Range],Criteria2!$D9))

Let's assume Criteria 1 is "A". This works great if the Criteria 1's range looks like this:

  • A
  • A
  • A
  • B (won't be included in list, as it does not match Criteria 1

If the list looks like this:

  • A
  • A
  • B
  • A

Then the dropdown list that produces the output of the formula is polluted, and in fact includes B.

What am I missing in my syntax?

user3731305
  • 13
  • 3
  • Any reason all of this logic has to be crammed into a named range? You could just add a couple columns to roll up the rows where critera=A and point your named range to that. – Jeff Jun 11 '14 at 18:54
  • Because then I would need many, many columns. The only way to mitigate this is to keep it dynamic. – user3731305 Jun 11 '14 at 18:58

0 Answers0