Match Index two criteria and return multiple values

[Jarlath]

Hello,

I am currently using the below formula:

=INDEX($B$40:$B$404,MATCH($B$5&$B$11,$C$40:$C$404&$D$40:$D$404,0),1)

I am matching b5 & b11 from cascading dependant drop down lists.

It works to return the first value I need in column B, but when I copy it down it still returns the same value rather than move to the next value.

For example it will return MON013 in the first cell, this code will appear in the cells below once copied down. Whereas I need to it return MON014,MON034 ( other applicable values from the table)

Any help would be great thanks.

 

[AhoyNC]

Maybe something like this.
This is an array formula and must be entered with CTRL-SHIFT-ENTER (if done right Excel will put {} around the formula).

=INDEX($B$40:$B$404,SMALL(IF($C$40:$C404=$B$5,IF($D$40:$D$404=$B$11,ROW($B$40:$B$404)-ROW($B$40)+1)),ROWS($B$40:B40)))

 

[Jarlath]

Worked perfectly, Thanks

 

[AhoyNC]

You're welcome. Glad that did the job. Thanks for the feedback.