SVERWEIS: Multiple columns - these options are available

The SVERWEIS function allows you to search your records in Microsoft Excel. However, SVERWEIS is limited because of the way it works. If you want to use SVERWEIS to search for multiple criteria in multiple columns, you need to extend the function.

Search multiple columns with multiple search criteria

The SVERWEIS function allows you to search a matrix or a range of cells for a specific search criterion. However, SVERWEIS can only search one search criterion and one column in the search matrix. To search multiple columns, you must combine the SVERWEIS function with the SELECT function and apply it as a matrix formula. The combination allows you to define multiple search criteria as well as multiple search range.The syntax of the combination looks like this: =SVERWEIS(searchcriterion1 & searchcriterion2;SELECT{};searchrange1; searchrange2);column;match)

• You use the "searchcriterion" argument to specify your search term. You can separate multiple criteria with "&" and list them one after the other.
• Within the curly brackets you specify, separated by a dot, how many matrices you want to search.
• Insert at "searchrange1" and "searchrange2" where to search within the matrix.
• The argument "column" specifies from which column SVERWEIS starts to search in the matrix.
• With the argument "match" you specify whether the search criterion must match exactly or imprecisely with the search results. The value 0 represents an exact search and 1 represents an inexact search.

Applied, the formula might look like this: =SVERWEIS(A2 & A3;SELECT{1.2};B2:B12;C2:C12)2;0)For the function to be applied as a matrix formula, you must press [CTRL] + [SHIFT] + [ENTER] when you have entered all arguments.