Reference: backwards - is it possible?

The Sreference is a useful tool that makes your work with Excel much easier. With the reference you search the columns of a matrix for a value and you can go back to the right from any value. Is it also possible that you align the search to the left?

This is how you search with Sverweis to the left

There are two possible solutions if you want to align a backward search with Sverweis to the left. The advantage of this way of searching is that you shorten the process. This is because, especially with very comprehensive data sets, it can be useful if you can control the search process in one direction.

1. The simplest solution is that you combine the index search with the Sverweis for comparisons. To do this, use the formula =INDEX(x;COMPARISON(x)). Replace the x in the formula with the parameters. The advantage of this is that you avoid the actual problem.
2. Alternatively, you can use an Sverweis with CHOICE. For this you use the formula: =SVERWEIS(x;WAHL((2/1);x);x). This way you force Excel to search also the left side of the search column. The difficulty here is in choosing the parameters you define for x. These must be so precise that you limit the search range exactly. Otherwise, this sreference will not work.

If you already have experience in formulating sreferences, then the second way is more effective, because you can make it more specific. Especially in the case of very large datasets, this can ensure that you save a lot of time. If you are not yet so familiar with the process, you should choose the first way to safely reach a result.