reverse match/index

[sejun87]

Hello, I got a quick question about match/index and its reverse function.

I'm fully familiar wth ow index and match function work,

But when it comes to the other way around, Im confused.

for instnce, if i have a data set as written below in column K-L, and I have a blank cell set like "As-Is" (A1:D4), and I want it to look like in "To-Be"(A9:D12)

Simply put, I want to see the head 1 value and find it on row 1 and do the same for head 2 with column A and put the exact value in the given cell, if there is any.

Any help would be much appreciated!
Thanks!!!

 

[Aladin Akyurek]

I think a sample is needed along with the desired results.

 

[sejun87]

http://foulball.co.kr/data/file/photo_2012/833590295_d5x6ac0T_aas.jpg

the image doesn't work for some reason...

 

[sejun87]

sorry, now the sample should appear just fine.

 

[shift-del]

Hi

Either with a formula:

=SUMPRODUCT(($K$3:$K$6=B$9)*($L$3:$L$6=$A10)*$M$3:$M$6)

Or with a pivot table:
Head1 in columns area, Head2 in rows area and Value in values area.

 

[Aladin Akyurek]

In B2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($M$3:$M$6,MATCH($A2,IF($K$3:$K$6=B$1,$L$3:$L$6),0)),"")

 

[mikerickson]

in B2 put =SUMIFS($M$3:$M$6, $K$3:$K$6, B$1, $L$3:$L$6, $A2) and drag down and right.

 

[sejun87]

Thanks! this works very fine. Let me study further and fully understand why I can't get this immediately.