# Index/Match with Multiple Criteria

##### Board Regular
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 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!

#### Marcelo Branco

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

M.

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

M.
Yes, my apologies!

#### Marcelo Branco

##### MrExcel MVP
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.

##### Board Regular
Perfect, thank you.

Last edited:

#### Marcelo Branco

##### MrExcel MVP
You are welcome. Glad to help.

M.

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

### 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...