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:
|
1 2 3 4 |
=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!