Excel: SVERWEIS to the left - use the function the other way around?

You can use a SVERWEIS to search for a value within a matrix and its first column and output any value to the right of it. Even though this is usually the only use case in practice, the question arises, however, whether you can also output a value to the left of the cell via a SVERWEIS.

Excel: SVERWEIS to the left - this is how it works

The given direction can be bypassed in two ways. In practice, however, usually only the first variant is suitable.

1. Given is a matrix whose value to be searched for is on A6. However, the cell that asks for this value is on B6, so to the right of it.
2. Now use the formula =INDEX(A1:A5:COMPARISON(A6;B1:B5;0)) in the cell B6.
3. The comparison formula searches through the specified cells a matrix with a column width of 1 and returns the searched value at a position within the search.
4. By the index formal now use the answer of the COMPARISON formula and thus return a value that is on the same row.

XVERWEIS in neuen Versionen?

1. Bekanntlich kann mit SVERWEIS nur nach rechts innerhalb einer Tabelle gesucht werden.
2. In neueren Versionen von Excel gibt es zu diesem Zweck jedoch nun die Formel XVERWEIS.
3. Mit dieser sind weitere Operationen möglich, sodass SVERWEIS grundsätzlich erweitert wurde.
4. Speziell die Suche in die linke Richtung kann nun durchgeführt werden.