Index/Match with Multiple Criteria

[kparadise]

Hello,

Looking for some help would would save lots of time and manual labor. It is a basic index match with 3 criteria; as well as obtaining the MAX value of the index.

So; the raw data looks like such.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]SHEET 1
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Group
[/TD]
[TD]Business
[/TD]
[TD]Control
[/TD]
[TD]Risk1
[/TD]
[TD]Risk2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]ABC
[/TD]
[TD][/TD]
[TD][/TD]
[TD]5
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]ABC
[/TD]
[TD][/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]ABC
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]ABC
[/TD]
[TD][/TD]
[TD][/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]ABC
[/TD]
[TD]ALT
[/TD]
[TD]control1
[/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]ABC
[/TD]
[TD]ALT
[/TD]
[TD]control1
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]ABC
[/TD]
[TD]ALT
[/TD]
[TD]control2
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]ABC
[/TD]
[TD]ALT
[/TD]
[TD]control2
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]ABC
[/TD]
[TD]ALT
[/TD]
[TD]control2
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]ABC
[/TD]
[TD]CNTR
[/TD]
[TD]control2
[/TD]
[TD]3
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]ABC
[/TD]
[TD]CNTR
[/TD]
[TD]control2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]ABC
[/TD]
[TD]CNTR
[/TD]
[TD]control1
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]ABC[/TD]
[TD]CNTR
[/TD]
[TD]control1
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]ABC
[/TD]
[TD]CNTR[/TD]
[TD]control1
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

I am trying to put together a heat map if you will. I will list all of the Controls down the left side of the table. I will then list the Business across the top. I will have to list the Business at the top two columns in a row; because I want the Risk1 and Risk2 of each Business next to each other. The last piece I would like to perform is to only list the MAX value from the RISK's column in the new table below.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]SHEET 2
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]ALT
[/TD]
[TD]ALT
[/TD]
[TD]CNTR
[/TD]
[TD]CNTR
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]Risk1
[/TD]
[TD]Risk2
[/TD]
[TD]Risk1
[/TD]
[TD]Risk2
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Control1
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]3
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Control2
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]5
[/TD]
[/TR]
</tbody>[/TABLE]

Now I know this formula needs to be an INDEX MATCH, I assume with multiple criteria (i.e. from Sheet 2; look up ALT, look up Risk1, and look up MAX(Control1) from SHEET 1. I just do not know the appropriate formula. Help!

 

[Marcelo Branco]

Question:
Shouldn't the result in C3 (Control1 - Alt - Risk2) be 2 rather than 4? Please, clarify.

M.

 

[kparadise]

Question:
Shouldn't the result in C3 (Control1 - Alt - Risk2) be 2 rather than 4? Please, clarify.

M.

Yes, my apologies!

 

[Marcelo Branco]

Try this array formula in Sheet2 B3 copied across and down
=MAX(IF(Sheet1!$B$2:$B$15=B$1,IF(Sheet1!$C$2:$C$15=$A3,INDEX(Sheet1!$D$2:$E$15,0,MATCH(B$2,Sheet1!$D$1:$E$1,0)))))
Ctrl+Shift+Enter

Hope this helps

M.

 

[kparadise]

Perfect, thank you.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.