reverse match/index

sejun87

New Member
Joined
Nov 11, 2018
Messages
11
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!!!
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

sejun87

New Member
Joined
Nov 11, 2018
Messages
11
http://foulball.co.kr/data/file/photo_2012/833590295_d5x6ac0T_aas.jpg

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

833590295_d5x6ac0T_aas.jpg
 

shift-del

Well-known Member
Joined
Aug 28, 2009
Messages
516
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi

Either with a formula:
Code:
=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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,915
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

New Member
Joined
Nov 11, 2018
Messages
11
Thanks! this works very fine. Let me study further and fully understand why I can't get this immediately.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,886
Messages
5,598,660
Members
414,252
Latest member
Tejeci

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top