Index/Match Formula Error

[brandiemz]

I'm trying to use an index, match formula to pull data from a table using two drop down boxes and a date. The formula pulls correctly if I have the first option selected in the first drop down but errors out if I select the second option in the drop down. Anyone have any suggestions on what I need to do to be able to use all the options in my drop down boxes? My formula is:
=INDEX('Tab 1'!$E$3:$ZZ$1200,MATCH($B$11,'Tab 1'!$D$3:$D$1200,0),MATCH($B$6,'Tab 1'!$A$3:$A$1200,0),MATCH(D$7,Tab 1'!$E$2:$ZZ$2,0))

where $B$11 and $B$6 are drop down boxes and D$7 is a date and is not a drop down box.

Thanks in advance.

 

[njimack]

INDEX has 2 argument, not 3. It's hard to give a specific solution since you haven't posted a sample of your data, but you might be better using SUMPRODUCT, or a SUM(IF array formula.

 

[Aladin Akyurek]

Looks like...

Control+shift+enter, not just enter:

=INDEX('Tab 1'!$E$3:$ZZ$1200,
   MATCH(1,IF('Tab 1'!$D$3:$D$1200=$B$11,IF(1'!$A$3:$A$1200=$B$6,1)),0),
   MATCH(D$7,Tab 1'!$E$2:$ZZ$2,0))

 

[T. Valko]

Looks like...

Control+shift+enter, not just enter:

=INDEX('Tab 1'!$E$3:$ZZ$1200,
MATCH(1,IF('Tab 1'!$D$3:$D$1200=$B$11,IF(1'!$A$3:$A$1200=$B$6,1)),0),
MATCH(D$7,Tab 1'!$E$2:$ZZ$2,0))

You left out the full sheet name in the 2nd IF.

Aren't you glad I'm here!