Excel – Index(Match) based on multiple columns

Posted on Posted in VBA \ Excel Tagged with , , ,

Everyone knows the feeling that they need to use the =Index(Match(),Match()) formula in Excel and something tiny was not enough to reach their goal. Some time ago I knew that something is missing I was trying to “filter” with the =Match()  formula, based on multiple values and I have managed to do it.

Imagine the following input table, where you are trying to filter by “Name”, “Surname” and “Number”:

As you see, I have somehow used a formula in “H4”, returnig “D1”, based on the input in “G”.

What is the formula? This:

=INDEX($A$1:$D$11,
MATCH(1,
($A$1:$A$11=H1)*($B$1:$B$11=H2)*($C$1:$C$11=H3),
0),4)

The idea is that it is an array formula (Ctrl + Shift + Enter), in order to calculate the arrays in the parenthesis (e.g. -> ($A$1:$A$11=H1)) returning a boolean value.

That’s all folks!