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!!!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
http://foulball.co.kr/data/file/photo_2012/833590295_d5x6ac0T_aas.jpg

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

833590295_d5x6ac0T_aas.jpg
 
Upvote 0
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.
 
Upvote 0
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)),"")
 
Upvote 0
Thanks! this works very fine. Let me study further and fully understand why I can't get this immediately.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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
Back
Top