Index/Match with Multiple Criteria

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
186
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.
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
ABCCNTR
control1
2
2
15
ABC
CNTRcontrol1
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!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Question:
Shouldn't the result in C3 (Control1 - Alt - Risk2) be 2 rather than 4? Please, clarify.

M.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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