Excel SVERWEIS: How to use the useful reference function

To find entries in a table column or row, you can simply use Excel's SVERWEIS. First, take a look at the structure of SVERWEIS to fully understand the function. Then, it's easy to place the formula in your Excel spreadsheet.

Excel SVERWEIS: What the formula means

1. With Excel SVERWEIS you can read individual values from Excel documents. The formula stands for the vertical search from top to bottom as well as for the horizontal search from left to right.
2. The search criterion names the term you want to search for.
3. The matrix specifies the range in which you search for the term.
4. The column index stands for the column with the searched information.
5. With the range respectively the reference you can optionally set how your search term should exactly match the value in the table or if you want to smooth the term.

Tips for using SVERWEIS

If you do not use SVERWEIS correctly, it quickly happens that you do not get all the results or the formula does not work at all.

1. It is therefore best to make sure that you use absolute references for range or reference.
2. If you search for numbers in the matrix, the table values must also be specified as numbers and not as text.
3. If the range takes the value "TRUE", you should first sort the first column of the matrix.
4. If you do not know some values, then enter question mark (=single character) and asterisk (=any string) as wildcard characters. To search for these two characters, enter a tilde (~) before the question mark or asterisk.