Match Column and Row with diagonal values

[keis386]

Hi,
I want to see the information of column J to V* in the columns B to H with a formula.
The problem is the information in column J to V* is diagonal to the the columns and rows in B to H

See attachment that would make it more clear (is a sample). In sheet 2 I also created the outcome but then with paste value/transpose.

What I also would prefer is that It will be done with a match formula that if I change a name in both sheets the other sheet adopts this change

* Only the Volume Column in the columns J to V
Thanks!

[Richard Buttrey]

Hi,

One way in B2 copied across and down

=INDEX($K$3:$V$9,MATCH(B$1,$J$3:$J$9,FALSE),MATCH($A2,$K$1:$V$1)-2)

[keis386]

Hi Thanks A lot,
One question if I copy the formulas to totally down to 19 January then I see in in row 5 to 19 the same amount, in this fields I would rather see a 0.
Is this possible

[Richard Buttrey]

Hi,

Move your dates to K1, N1, Q1 etc. and use

IFERROR(INDEX($K$3:$V$9,MATCH(B$1,$J$3:$J$9,FALSE),MATCH($A2,$K$1:$V$1,FALSE)),0)

[twiggywales]

you could use sumproduct if your workig with numbers

try this

=SUMPRODUCT(($K$1:$V$1=$A2)*($J$3:$J$9=B$1),$K$3:$V$9)

(and switch your 0s to view on in excel options)

[keis386]

Thanks a lot! Problem solved