SVERWEIS: Alternative - these options are available

The SVERWEIS function allows you to search lists for specific data in Microsoft Excel. However, in many situations, the function is not flexible enough to produce the desired results. The reason for this is the way it works. Learn what alternatives to the SVERWEIS function are available to you.

INDEX and COMPARISON function as an alternative

If you want to search for data in a list in Excel and then output the search result, the function intended for this purpose is the SVERWEIS function. The problem with the function, however, is that its approach often makes it inflexible in practice.

• One of the reasons for this is how the function takes the search criterion. For the function to work properly, this criterion must always be in the first column of the search matrix. However, this is often not the case in practice.
• Alternatively to the SVERWEIS function, you can combine the functions INDEX and COMPARISON with each other.
• The advantage of the combination is that you now also have, for example, the possibility to read out data to the left of the search column and not only to the right of the search column as with the SVERWEIS function.

This is how you apply the combination: INDEX and COMPARISON

In Excel, you can combine a variety of functions and formulas to extend their functionality.

• You apply the INDEX function using the formula "=INDEX(matrix; row; [column])".
• The syntax for the COMPARISON function is: "=COMPARISON(search criterion;search matrix;[comparison type])".

If you combine the two functions together, the new syntax is: "=INDEX(matrix; COMPARE(search criterion;matrix)". Mit der Kombination können Sie dann Listen beziehungsweise eine Matrix nach einem bestimmten Suchkriterium durchsuchen – ohne die Einschränkungen der SVERWEIS-Funktion.