Combining Sreference and Wreference - Finding Intersection

Frequently, Sreference and Wreference are necessary tools for your work with Excel. However, the concept is that you can only ever search columns or rows. This can make it difficult to find a value that depends on parameters from the rows as well as the columns.

Sverweis and Wverweis: How to find the intersection

To find the intersection between the two references, it is possible that you combine both. For this, it is important that you have an overview of the values so that the formula works. In general, you can work with two variants here, the first being kept simpler and the second option being used for more complex data sets.

1. For simple data sets, use the formula =SVERWEIS (x;x;x; VERGLEICH (y;y;y;0);0). This takes the selected parameters, compares them and finds the desired intersection in your data set.
2. You can also use the more complex formula, which is necessary to keep track of comprehensive data sets. For this, you should work with the index and the following formula: =INDEX(x;y;COMPARISON (x;x;x;0);COMPARISON(y;y;y;0)). For the y in the formula put the values of the rows and for the x put the values of the columns. By comparing the different points, you get the intersection.

This is what you should pay attention to

If you have not had much experience with the Excel tools, you should first try both formulas on data sets that you can survey. This will allow you to check your results and thus the correct application. This way you will prevent sources of errors that can affect your work.