SVERWEIS with three search criteria - here's how to do it

You can use the SVERWEIS function in Microsoft Excel to search data sets for a specific criterion. However, if you want to provide the SVERWEIS with more than one search criterion, you have to trick, because the basic function does not support the operation.

How to use the SVERWEIS with multiple search criteria

The basic function of the SVERWEIS is designed for only one search criterion. However, if you combine the SVERWEIS with the SELECT function, you can search your records for more than one criterion.

1. The syntax of the SVERWEIS function usually looks like this: "=SVERWEIS (search criterion; matrix; column index; match)".
2. The syntax of the CHOOSE function is composed of the following arguments: "=CHOOSE(index; value1; [value2];...)".
3. Combining the two functions and adding multiple search criteria results in the syntax: "=SVERWEIS (search criterion1 & search criterion2 & search criterion3; SELECT({1.2.3}; matrix containing search criterion1 & matrix containing search criterion2 & matrix containing search criterion3); column index; match)".
4. The combined version of the two functions is largely still the basic function of the SVERWEIS, only the argument "matrix" is exchanged with the SELECT function.
5. Dank der integrierten WAHL-Funktion kann der SVERWEIS nun mehrere Suchkriterien verarbeiten.

Wichtig zu wissen ist abschießend, dass Sie die komplette Formel mit der Tastenkombination [STRG] + [SHIFT] + [ENTER] schließen müssen, damit Excel eine Matrixformel erzeugt. Nur dann funktionieren die beiden miteinander kombinierten Funktionen wie gewünscht.