Anzeige

The Xreference function – difference to Sreference and Wreference

The Vreference and Hreference functions have been around in Excel for a very long time, and they’re also great for querying specific data from a table based on a search criteria. In August 2019, however, a new function in Excel was released as a beta version, which is now also fully available for users of Office 365 and Office 2021. It is about the Xreference in Excel, with which you can not only save on Vreference and Wreference, but this function also fixes some weaknesses that were there in the previously mentioned functions and where users had to work with workarounds again and again to fix them bypass.

Die Xverweis Funktion - Unterschied zu Sverweis und Wverweis

Topic Overview

Anzeige

As always, Office 365 users are ahead of the game when it comes to new functions, but over the years the Xreference should be everywhere.
In our little tutorial, we would like to explain the direct comparison using a practice-oriented example, and here you will quickly see where the weaknesses of the Vreference are compared to the Xreference.

The Xreference function – difference to Sreference and Wreference

The Vreference and Hreference functions have been around in Excel for a very long time, and they’re also great for querying specific data from a table based on a search criteria. In August 2019, however, a new function in Excel was released as a beta version, which is now also fully available for users of Office 365 and Office 2021. It is about the Xreference in Excel, with which you can not only save on Vreference and Wreference, but this function also fixes some weaknesses that were there in the previously mentioned functions and where users had to work with workarounds again and again to fix them bypass.

Die Xverweis Funktion - Unterschied zu Sverweis und Wverweis

Topic Overview

Anzeige

As always, Office 365 users are ahead of the game when it comes to new functions, but over the years the Xreference should be everywhere.
In our little tutorial, we would like to explain the direct comparison using a practice-oriented example, and here you will quickly see where the weaknesses of the Vreference are compared to the Xreference.

How does the Xreference work in Excel

In order to show the functionality and the difference to Sreference and Wreference in Excel as transparently and practically as possible, we have created a sample table with different products from a furniture store including batch numbers, versions and storage locations. The aim should be to use a number [column B] to automatically display all the remaining properties of the product in question.

So far that’s not a problem at all and can be easily solved as usual either with an Vreference or an index function. In our case, however, the main focus should be on the weaknesses of the V reference and how we can solve them better using the Xreference.

See fig. (click to enlarge)

Beispiel Xverweis und Sverweis
Advertisement

With the Xreference, the formula looks like this when we enter the number [ex. No.12] want to determine the remaining values:

=XREFERENCE(J4;table1[number];table1

;”not found”;0;1)

With the Sreference, the formula looks a bit more spartan if you want to determine the remaining values:

=VREFERENCE(J13,Table1[[Number]:[ProductName]],2,TRUE)

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.

Difference: 1
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.

Difference: 2
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.

Difference: 3
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)

Falscher Wert bei Sverweis vs. Xverweis
Spalte verschoben -Sverweis gibt Fehler aus
Advertisement

As you have certainly seen, the Xreference offers advantages and more potential compared to the Sreference and the Wreference. And of course you can nest and combine the Xreference with other functions in Excel as well. For all those who want to have this function in any case, as already mentioned, the only option is to buy Office 2021 or Office 365.

You can download the table used here free of charge and try it out a bit.

How does the Xreference work in Excel

In order to show the functionality and the difference to Sreference and Wreference in Excel as transparently and practically as possible, we have created a sample table with different products from a furniture store including batch numbers, versions and storage locations. The aim should be to use a number [column B] to automatically display all the remaining properties of the product in question.

So far that’s not a problem at all and can be easily solved as usual either with an Vreference or an index function. In our case, however, the main focus should be on the weaknesses of the V reference and how we can solve them better using the Xreference.

See fig. (click to enlarge)

Beispiel Xverweis und Sverweis
Advertisement

With the Xreference, the formula looks like this when we enter the number [ex. No.12] want to determine the remaining values:

=XREFERENCE(J4;table1[number];table1

;”not found”;0;1)

With the Sreference, the formula looks a bit more spartan if you want to determine the remaining values:

=VREFERENCE(J13,Table1[[Number]:[ProductName]],2,TRUE)

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.

Difference: 1
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.

Difference: 2
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.

Difference: 3
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)

Falscher Wert bei Sverweis vs. Xverweis
Spalte verschoben -Sverweis gibt Fehler aus
Advertisement

As you have certainly seen, the Xreference offers advantages and more potential compared to the Sreference and the Wreference. And of course you can nest and combine the Xreference with other functions in Excel as well. For all those who want to have this function in any case, as already mentioned, the only option is to buy Office 2021 or Office 365.

You can download the table used here free of charge and try it out a bit.

Bestseller Office Software

Microsoft 365 Family | 6 Nutzer | Mehrere PCs/Macs, Tablets und mobile Geräte | 1 Jahresabonnement |Download CodeMicrosoft Office 2021 | Home & Student | 1 Gerät | 1 Benutzer | PC/Mac | Aktivierungscode per EmailMicrosoft 365 Family | 6 Nutzer | Mehrere PCs/Macs, Tablets und mobile Geräte | 1 Jahresabonnement |BoxMicrosoft 365 Single | 1 Nutzer | Mehrere PCs/Macs, Tablets und mobile Geräte | 1 Jahresabonnement | Download CodeMicrosoft 365 Family 12+3 Monate | 6 Nutzer | Mehrere PCs/Macs, Tablets & mobile Geräte | Download Code + NORTON 360 Deluxe | 5 Geräte |15 Monate mit Automatischer Verlängerung| Download Code
LinkMicrosoft 365 Family | 6 Nutzer | Mehrere PCs/Macs, Tablets und mobile Geräte | 1 Jahresabonnem*Microsoft Office 2021 | Home & Student | 1 Gerät | 1 Benutzer | PC/Mac | Aktivierungscode p*Microsoft 365 Family | 6 Nutzer | Mehrere PCs/Macs, Tablets und mobile Geräte | 1 Jahresabonnem*Microsoft 365 Single | 1 Nutzer | Mehrere PCs/Macs, Tablets und mobile Geräte | 1 Jahresabonnem*Microsoft 365 Family 12+3 Monate | 6 Nutzer | Mehrere PCs/Macs, Tablets & mobile Geräte | D*
Price€ 99.00 € 75.99
Price incl. VAT., Excl. Shipping
€ 149.99 € 88.99
Price incl. VAT., Excl. Shipping
€ 99.00 € 79.99
Price incl. VAT., Excl. Shipping
€ 69.00 € 55.99
Price incl. VAT., Excl. Shipping
€ 183.99 € 75.99
Price incl. VAT., Excl. Shipping
AvailabilityJetzt als Download verfügbar.Jetzt als Download verfügbar.Auf Lager.Jetzt als Download verfügbar.Jetzt als Download verfügbar.
zum Angebot*zum Angebot*zum Angebot*zum Angebot*zum Angebot*
Microsoft 365 Family | 6 Nutzer | Mehrere PCs/Macs, Tablets und mobile Geräte | 1 Jahresabonnement |Download Code
AvailabilityJetzt als Download verfügbar.
€ 99.00
€ 75.99
Price incl. VAT., Excl. Shipping
Microsoft Office 2021 | Home & Student | 1 Gerät | 1 Benutzer | PC/Mac | Aktivierungscode per Email
AvailabilityJetzt als Download verfügbar.
€ 149.99
€ 88.99
Price incl. VAT., Excl. Shipping
Microsoft 365 Family | 6 Nutzer | Mehrere PCs/Macs, Tablets und mobile Geräte | 1 Jahresabonnement |Box
AvailabilityAuf Lager.
€ 99.00
€ 79.99
Price incl. VAT., Excl. Shipping
Microsoft 365 Single | 1 Nutzer | Mehrere PCs/Macs, Tablets und mobile Geräte | 1 Jahresabonnement | Download Code
AvailabilityJetzt als Download verfügbar.
€ 69.00
€ 55.99
Price incl. VAT., Excl. Shipping
*Preise aktualisiert am 21. May 2022 um 7:48 . Angaben ohne Gewähr.

Bestseller Office Software

Microsoft 365 Family | 6 Nutzer | Mehrere PCs/Macs, Tablets und mobile Geräte | 1 Jahresabonnement |Download CodeMicrosoft Office 2021 | Home & Student | 1 Gerät | 1 Benutzer | PC/Mac | Aktivierungscode per EmailMicrosoft 365 Family | 6 Nutzer | Mehrere PCs/Macs, Tablets und mobile Geräte | 1 Jahresabonnement |BoxMicrosoft 365 Single | 1 Nutzer | Mehrere PCs/Macs, Tablets und mobile Geräte | 1 Jahresabonnement | Download CodeMicrosoft 365 Family 12+3 Monate | 6 Nutzer | Mehrere PCs/Macs, Tablets & mobile Geräte | Download Code + NORTON 360 Deluxe | 5 Geräte |15 Monate mit Automatischer Verlängerung| Download Code
LinkMicrosoft 365 Family | 6 Nutzer | Mehrere PCs/Macs, Tablets und mobile Geräte | 1 Jahresabonnem*Microsoft Office 2021 | Home & Student | 1 Gerät | 1 Benutzer | PC/Mac | Aktivierungscode p*Microsoft 365 Family | 6 Nutzer | Mehrere PCs/Macs, Tablets und mobile Geräte | 1 Jahresabonnem*Microsoft 365 Single | 1 Nutzer | Mehrere PCs/Macs, Tablets und mobile Geräte | 1 Jahresabonnem*Microsoft 365 Family 12+3 Monate | 6 Nutzer | Mehrere PCs/Macs, Tablets & mobile Geräte | D*
Price€ 99.00 € 75.99
Price incl. VAT., Excl. Shipping
€ 149.99 € 88.99
Price incl. VAT., Excl. Shipping
€ 99.00 € 79.99
Price incl. VAT., Excl. Shipping
€ 69.00 € 55.99
Price incl. VAT., Excl. Shipping
€ 183.99 € 75.99
Price incl. VAT., Excl. Shipping
AvailabilityJetzt als Download verfügbar.Jetzt als Download verfügbar.Auf Lager.Jetzt als Download verfügbar.Jetzt als Download verfügbar.
zum Angebot*zum Angebot*zum Angebot*zum Angebot*zum Angebot*
Microsoft 365 Family | 6 Nutzer | Mehrere PCs/Macs, Tablets und mobile Geräte | 1 Jahresabonnement |Download Code
AvailabilityJetzt als Download verfügbar.
€ 99.00
€ 75.99
Price incl. VAT., Excl. Shipping
Microsoft Office 2021 | Home & Student | 1 Gerät | 1 Benutzer | PC/Mac | Aktivierungscode per Email
AvailabilityJetzt als Download verfügbar.
€ 149.99
€ 88.99
Price incl. VAT., Excl. Shipping
Microsoft 365 Family | 6 Nutzer | Mehrere PCs/Macs, Tablets und mobile Geräte | 1 Jahresabonnement |Box
AvailabilityAuf Lager.
€ 99.00
€ 79.99
Price incl. VAT., Excl. Shipping
Microsoft 365 Single | 1 Nutzer | Mehrere PCs/Macs, Tablets und mobile Geräte | 1 Jahresabonnement | Download Code
AvailabilityJetzt als Download verfügbar.
€ 69.00
€ 55.99
Price incl. VAT., Excl. Shipping
*Preise aktualisiert am 21. May 2022 um 7:48 . Angaben ohne Gewähr.

Search the net for other topics:

About the author:

Michael W. Suhr
Michael W. SuhrWebdesigner / MBA
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:





You might also be interested in:

Search the net for other topics:

About the author:

Michael W. Suhr
Michael W. SuhrWebdesigner / MBA
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:





You might also be interested in:

Popular Posts:

305, 2022

The Xreference function – difference to Sreference and Wreference

May 3rd, 2022|Categories: Microsoft Excel, Microsoft Office, Uncategorized|Tags: , |

Den Sverweis und den Wverweis kennt so ziemlich jeder der Excel verwendet. Etwas neuer hingegen ist der Xverweis mit dem sich einige Schwächen der bislang genutzten Funktionen ausbügeln lassen. Wir erklären die Unterschiede von Xverweis zu Sverweis & Wverweis.

1509, 2021

Microsoft Excel: Create vacation planner 2022 with public holidays and weekends

September 15th, 2021|Categories: Uncategorized, Microsoft Excel, Microsoft Office, Office 365|Tags: , , , , |

We explain how you can create your own vacation planner 2022 in Microsoft Excel. And of course with a display of public holidays and weekends.

1409, 2021

Create annual calendar 2022 in Microsoft Excel

September 14th, 2021|Categories: Microsoft Excel, Microsoft Office, Office 365|Tags: , , , , |

In our tutorial we describe how you can create an annual calendar for 2022 with a display of the calendar week and public holidays in Excel, and use it anew every year.

Word & Excel Templates:

See more

Word CV Templates:

See more

Monthly Bestseller:

Smart-Home

Bestseller Kategorie Smart-Home
Angebote ansehen

Homeoffice

Bestseller Kategorie Homeoffice
Angebote ansehen

Smartphone & Zubehör

Bestseller Kategorie Smartphone und Zubehoer
Angebote ansehen

Popular Posts:

305, 2022

The Xreference function – difference to Sreference and Wreference

May 3rd, 2022|Categories: Microsoft Excel, Microsoft Office, Uncategorized|Tags: , |

Den Sverweis und den Wverweis kennt so ziemlich jeder der Excel verwendet. Etwas neuer hingegen ist der Xverweis mit dem sich einige Schwächen der bislang genutzten Funktionen ausbügeln lassen. Wir erklären die Unterschiede von Xverweis zu Sverweis & Wverweis.

1509, 2021

Microsoft Excel: Create vacation planner 2022 with public holidays and weekends

September 15th, 2021|Categories: Uncategorized, Microsoft Excel, Microsoft Office, Office 365|Tags: , , , , |

We explain how you can create your own vacation planner 2022 in Microsoft Excel. And of course with a display of public holidays and weekends.

1409, 2021

Create annual calendar 2022 in Microsoft Excel

September 14th, 2021|Categories: Microsoft Excel, Microsoft Office, Office 365|Tags: , , , , |

In our tutorial we describe how you can create an annual calendar for 2022 with a display of the calendar week and public holidays in Excel, and use it anew every year.

Word & Excel Templates:

See more

Word CV Templates:

See more

Monthly Bestseller:

Smart-Home

Bestseller Kategorie Smart-Home
Angebote ansehen

Homeoffice

Bestseller Kategorie Homeoffice
Angebote ansehen

Smartphone & Zubehör

Bestseller Kategorie Smartphone und Zubehoer
Angebote ansehen
2022-05-04T07:16:56+02:00By |Categories: Microsoft Excel, Microsoft Office, Uncategorized|Tags: , |

Title

Anzeige

Most read:

Search by category:

Go to Top