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.
<tbody>
</tbody>
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.
<tbody>
</tbody>
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!
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.
SHEET 1 | A | B | C | D | E |
1 | Group | Business | Control | Risk1 | Risk2 |
2 | ABC | 5 | 4 | ||
3 | ABC | 3 | 2 | ||
4 | ABC | 2 | 2 | ||
5 | ABC | 5 | 3 | ||
6 | ABC | ALT | control1 | 4 | 1 |
7 | ABC | ALT | control1 | 3 | 2 |
8 | ABC | ALT | control2 | 4 | 2 |
9 | ABC | ALT | control2 | 5 | 3 |
10 | ABC | ALT | control2 | 4 | 2 |
11 | ABC | CNTR | control2 | 3 | 5 |
12 | ABC | CNTR | control2 | 3 | 4 |
13 | ABC | CNTR | control1 | 3 | 3 |
14 | ABC | CNTR | control1 | 2 | 2 |
15 | ABC | CNTR | control1 | 5 | 1 |
<tbody>
</tbody>
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.
SHEET 2 | A | B | C | D | E |
1 | ALT | ALT | CNTR | CNTR | |
2 | Risk1 | Risk2 | Risk1 | Risk2 | |
3 | Control1 | 4 | 4 | 5 | 3 |
4 | Control2 | 5 | 3 | 3 | 5 |
<tbody>
</tbody>
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!