Multiple ROWS and COLUMS

[Trebby]

Hi All,

Please can you assist.

I am trying to pull a number of rows and columns together based on criteria within a INDEX,MATCH formula.


[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl68, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]C
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]D
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]E
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]Code
[/TD]
[TD="bgcolor: transparent"]Name
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]Jan-17
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]Feb-17
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]Mar-17
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"]John
[/TD]
[TD="bgcolor: transparent, align: right"]10
[/TD]
[TD="bgcolor: transparent, align: right"]20
[/TD]
[TD="bgcolor: transparent, align: right"]30
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]John
[/TD]
[TD="bgcolor: transparent, align: right"]20
[/TD]
[TD="bgcolor: transparent, align: right"]30
[/TD]
[TD="bgcolor: transparent, align: right"]40
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent"]John
[/TD]
[TD="bgcolor: transparent, align: right"]30
[/TD]
[TD="bgcolor: transparent, align: right"]40
[/TD]
[TD="bgcolor: transparent, align: right"]50
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"]Ben
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent, align: right"]10
[/TD]
[TD="bgcolor: transparent, align: right"]15
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]Ben
[/TD]
[TD="bgcolor: transparent, align: right"]10
[/TD]
[TD="bgcolor: transparent, align: right"]15
[/TD]
[TD="bgcolor: transparent, align: right"]20
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent"]Ben
[/TD]
[TD="bgcolor: transparent, align: right"]15
[/TD]
[TD="bgcolor: transparent, align: right"]20
[/TD]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[/TR]
</tbody>[/TABLE]

Results

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl68, width: 64, bgcolor: transparent"]G
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]H
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]I
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]J
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]Jan-17
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]Feb-17
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"]John
[/TD]
[TD="bgcolor: transparent, align: right"]10
[/TD]
[TD="bgcolor: transparent, align: center"]#REF !
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"]Ben
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent, align: center"]#REF !
[/TD]
[/TR]
</tbody>[/TABLE]


For January I am using the following formula

=INDEX($C$3:$E$8,MATCH($H3,$B$3:$B$8,0),MATCH($G3,$A$3:$A$8,0),MATCH($I$2,$C$2:$E$2,0))

When dragging this over for Feb I get #REF !

=INDEX($C$3:$E$8,MATCH($H3,$B$3:$B$8,0),MATCH($G3,$A$3:$A$8,0),MATCH($J$2,$C$2:$E$2,0))

Can you advise what I am doing wrong or maybe suggest an alternative way of doing this?

Thanks for your help in advance.

 

[njimack]

Array formula, so confirm with ctrl+shift+enter:
=INDEX($C$2:$E$7,MATCH($G2&$H2,$A$2:$A$7&$B$2:$B$7,0),MATCH(I$1,$C$1:$E$1,0))

 

[Trebby]

Array formula, so confirm with ctrl+shift+enter:
=INDEX($C$2:$E$7,MATCH($G2&$H2,$A$2:$A$7&$B$2:$B$7,0),MATCH(I$1,$C$1:$E$1,0))

Worked like a charm... Thank you.