Excel – Index(Match) based on multiple columns

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:

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!

Tagged with: , , ,