Index/Match with Multiple Criteria

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
169
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!
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
Question:
Shouldn't the result in C3 (Control1 - Alt - Risk2) be 2 rather than 4? Please, clarify.

M.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
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.
 

Forum statistics

Threads
1,081,575
Messages
5,359,725
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top