Both functions serve their purpose, but work quite differently. Because while the Vreference reacts immediately with error messages as soon as there are changes to the basic table, the Xreference is much more flexible.
If we move column B to a different position in our original table and swap it with column C , for example, there are immediately error messages with the Sreference because the references are missing. Because the search direction for the reference is always from left to right. The Xreference, on the other hand, changes its search direction automatically when columns or rows are moved and thus still returns the correct results.
With the Xreference, we have the option of an alternative [e.g. Text] to enter if a value should not be found. This is sort of a built-in IF-THEN function. In this way, user-defined error messages [in our example “not found”] can be output if a value is not found in the list.
If we go beyond the list with a search for the Sreference and look for the number 16 [which does not exist] in relation to our example table, then instead of error messages, the results of the last entry are simply output. So here are the product specifications of number 15. With a list as small as ours, that’s still possible, but consider a list with hundreds or even thousands of lines. Then you will very quickly have incorrect results.
See fig. (click to enlarge)