The Sreference and the Wreference in Excel

If you have created a table with many records in Excel, you may eventually come to a point where certain records should be automatically retrieved from this table and pasted into another table for a better overview.

So what do you do?

Of course you have the possibility to create one or more stand-alone tables with the Pivot function, and of course have a great variety of filter options at the same time.

But sometimes it does not work the way you had imagined.

And this is exactly where the S-reference and W-reference functions come into play.

How these two variants work and how to use them I would like to shed some light on in this article.

s-reference and w-reference in excel

Topic Overview

Anzeige

The Sreference and the Wreference in Excel

If you have created a table with many records in Excel, you may eventually come to a point where certain records should be automatically retrieved from this table and pasted into another table for a better overview.

So what do you do?

Of course you have the possibility to create one or more stand-alone tables with the Pivot function, and of course have a great variety of filter options at the same time.

But sometimes it does not work the way you had imagined.

And this is exactly where the S-reference and W-reference functions come into play.

How these two variants work and how to use them I would like to shed some light on in this article.

s-reference and w-reference in excel

Topic Overview

Anzeige

1. Definition S-reference and W-reference

1. Definition S-reference and W-reference

In general we first clarify the definition of the S-reference and W-reference.

The S-reference:

The “S” stands for “vertical” and describes the search direction in which Excel searches for a specific term.
What does this mean in columns for a term is searched, and if this is found is searched in the appropriate line to determine the coordinate.

see picture: (click to enlarge)

Senkrechte Suchrichtung
Advertisement

The W reference:

The “W” stands for “horizontal” and also describes the search direction in which Excel searches for a specific term.
Only here is not determined first in columns but in lines for a specific term to determine the coordinate.

see picture: (click to enlarge)

Waagerechte Suchrichtung
cshow
Ads

In general we first clarify the definition of the S-reference and W-reference.

The S-reference:

The “S” stands for “vertical” and describes the search direction in which Excel searches for a specific term.
What does this mean in columns for a term is searched, and if this is found is searched in the appropriate line to determine the coordinate.

see picture: (click to enlarge)

Senkrechte Suchrichtung
Advertisement

The W reference:

The “W” stands for “horizontal” and also describes the search direction in which Excel searches for a specific term.
Only here is not determined first in columns but in lines for a specific term to determine the coordinate.

see picture: (click to enlarge)

Waagerechte Suchrichtung
cshow
Ads

2. Procedure S-reference

2. Procedure S-reference

In order to make the whole thing a bit more plastic let’s assume that we want to build a bill template in Excel, but do not want to have to enter all the recipient data (name, street, city) every time.

Then we should begin by creating a suitable table of customer master data that will later serve as the data source for our S reference. Next, of course, we need the destination (our invoice template).

In the invoice, the recipient address is to be filled in automatically after entering the customer number.

see picture: (click to enlarge)

Beispiel S Verweis

For the automated filling of the receiver head to work, we now need to build several S references that all use the same source but need different coordinates to output the correct data set.

The first entry: first name

We click in the cell “First name” and start by entering the formula as follows:

= LOOKUP (

  • After this input, Excel will already tell us which information is needed first. This is the search criteria.
  • Here we have to click on the cell in which we will enter our customer number later.

What does Excel do after exactly what is entered there will look in the data source.

see picture: (click to enlarge)

Suchkriterium festlegen
cshow
Ads

Next, Excel asks for the matrix which is nothing but the table area in which to search for the search criterion (our customer number).

In our example, we can easily mark the entire table with our customer master data.

see picture: (click to enlarge)

Matrix festlegen

Be sure to remember all the arguments in the formula are each with a semicolon; to separate.

The next step asks for the column index.
Here is simply counted. And in the wievielt column of the previously marked table (not the entire worksheet!) Is to be searched for the entry in the field “first name” should.

In our example, it is the third column (in which the first names are). Thus, we simply enter a 3 here.

The last part of our S-reference is not entirely unimportant, because it asks for true = 1 or false = 0.

This is a bit confusing at first, but ultimately means nothing else if Excel should search for exactly the search criterion (as it was entered), or whether it may be similar.

For our example, of course, we are looking for exactly the name entered, and not one of the similar.
So we enter a “0”.

Our finished S-reference for the first field “first name” should look like this.

see picture: (click to enlarge)

S-Verweis fertige Formel

In order to make the whole thing a bit more plastic let’s assume that we want to build a bill template in Excel, but do not want to have to enter all the recipient data (name, street, city) every time.

Then we should begin by creating a suitable table of customer master data that will later serve as the data source for our S reference. Next, of course, we need the destination (our invoice template).

In the invoice, the recipient address is to be filled in automatically after entering the customer number.

see picture:

Beispiel S Verweis

For the automated filling of the receiver head to work, we now need to build several S references that all use the same source but need different coordinates to output the correct data set.

The first entry: first name

We click in the cell “First name” and start by entering the formula as follows:

= LOOKUP (

  • After this input, Excel will already tell us which information is needed first. This is the search criteria.
  • Here we have to click on the cell in which we will enter our customer number later.

What does Excel do after exactly what is entered there will look in the data source.

see picture:

Suchkriterium festlegen
cshow
Ads

Next, Excel asks for the matrix which is nothing but the table area in which to search for the search criterion (our customer number).

In our example, we can easily mark the entire table with our customer master data.

see picture:

Matrix festlegen

Be sure to remember all the arguments in the formula are each with a semicolon; to separate.

The next step asks for the column index.
Here is simply counted. And in the wievielt column of the previously marked table (not the entire worksheet!) Is to be searched for the entry in the field “first name” should.

In our example, it is the third column (in which the first names are). Thus, we simply enter a 3 here.

The last part of our S-reference is not entirely unimportant, because it asks for true = 1 or false = 0.

This is a bit confusing at first, but ultimately means nothing else if Excel should search for exactly the search criterion (as it was entered), or whether it may be similar.

For our example, of course, we are looking for exactly the name entered, and not one of the similar.
So we enter a “0”.

Our finished S-reference for the first field “first name” should look like this.

see picture:

S-Verweis fertige Formel

3. Procedure W-reference

3. Procedure W-reference

The W reference is relatively similar to the S reference.

The main difference lies in the search direction.
It is not searched vertically but horizontally. To stay with our calculation example, I simply transposed the table with the data source (lines and columns reversed) to represent the W reference.

The function with which we come to our “first name” in the bill is therefore:

= HLOOKUP (

Then again specify the cell with the search criterion (the one with the customer number)

next the matrix area (the whole table with the master data)

and now, instead of a column index, just get a row index.
Again, we just count by again. And in which line should be searched for the first name.

This is the third line in our example. So we enter the 3 at the line index.
And finally, we enter the “0” for “False” to search for an exact match.

Our complete function should look like this.

see picture: (click to enlarge)

W-Verweis fertige Formel
Kauf auch du bei der Nummer 1!
Anzeige

Both the S-reference and the W-reference can be used in such a variety of ways, and lead depending on the starting position of the data source to one and the same result.

Of course, you can extend the whole range of functions considerably, and nest them with other functions as well (for example with an if-then function), but already in the basic version, some of the work can be done well with these rather simple functions.

Blogverzeichnis Bloggerei.de

The W reference is relatively similar to the S reference.

The main difference lies in the search direction.
It is not searched vertically but horizontally. To stay with our calculation example, I simply transposed the table with the data source (lines and columns reversed) to represent the W reference.

The function with which we come to our “first name” in the bill is therefore:

= HLOOKUP (

Then again specify the cell with the search criterion (the one with the customer number)

next the matrix area (the whole table with the master data)

and now, instead of a column index, just get a row index.
Again, we just count by again. And in which line should be searched for the first name.

This is the third line in our example. So we enter the 3 at the line index.
And finally, we enter the “0” for “False” to search for an exact match.

Our complete function should look like this.

see picture: (click to enlarge)

Der W-Verweis ist dem S-Verweis relativ gleich.

Der Hauptunterschied liegt hierbei in der Suchrichtung. Es wird hier also nicht senkrecht sondern waagerecht gesucht. Um bei unserem Rechnungsbeispiel zu bleiben, habe ich die Tabelle mit der Datenquelle einfach mal transponiert (Zeilen und Spalten vertauscht) um den W-Verweis darzustellen.

Die Funktion mit der wir zu unserem “Vornamen” in der Rechnung kommen lautet demnach: =WVERWEIS(

Dann erneut die Zelle mit dem Suchkriterium festlegen (Die mit der Kundennummer) als nächstes den Matrixbereich (Die gesamte Tabelle mit den Stammdaten) und nun kommt statt einem Spaltenindex einfach ein Zeilenindex.

Auch hier zählen wir einfach wieder durch. Und zwar in welcher Zeile soll nach dem Vornamen gesucht werden.

Dies ist in unserem Beispiel die dritte Zeile. Also geben wir beim Zeilenindex die 3 ein. Und zum Schluss geben wir noch die “0” für “Falsch” ein, damit nach einer genauen Übereinstimmung gesucht wird.

Unsere komplette Funktion sollte dann wie folgt aussehen.

siehe Abb.:

W Verweis fertige Formel
Kauf auch du bei der Nummer 1!
Anzeige

Both the S-reference and the W-reference can be used in such a variety of ways, and lead depending on the starting position of the data source to one and the same result.

Of course, you can extend the whole range of functions considerably, and nest them with other functions as well (for example with an if-then function), but already in the basic version, some of the work can be done well with these rather simple functions.

Blogverzeichnis Bloggerei.de

Search for other topics:

About the Author:

Michael W. Suhr
Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
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:

Search for other topics:

About the Author:

Michael W. Suhr
Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
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:

Popular Posts:

2306, 2023

With the webcam to your dream job: Preparation for the video job interview!

June 23rd, 2023|Categories: Career, Hardware, Homeoffice, Shorts & Tutorials|Tags: , , |

Streamline your video interview with our tips on technique, setting, dress, body language and preparation. It's not quite success from the sofa, but this is where you can prove your professionalism.

1805, 2023

IT security: The deceptive appearance of digital security

May 18th, 2023|Categories: Data Protection, Hardware, Homeoffice, Software|Tags: , |

In a digitized world, IT security is an important factor. But technology alone is not enough. A holistic strategy is needed that also takes into account the human component.

2104, 2023

Create Excel Budget Book – with Statistics – Tutorial

April 21st, 2023|Categories: Internet, Finance & Shopping, Microsoft Excel, Microsoft Office, Office 365, Shorts & Tutorials|Tags: , , , |

Create your own Excel budget book with a graphical dashboard, statistics, trends and data cut-off. A lot is possible with pivot tables and pivot charts.

1504, 2023

Excel Database with Input Form and Search Function

April 15th, 2023|Categories: Microsoft Excel, Microsoft Office, Office 365, Shorts & Tutorials|Tags: , |

So erstellen Sie eine Datenbank mit Eingabemaske und Suchfunktion OHNE VBA KENNTNISSE in Excel ganz einfach. Durch eine gut versteckte Funktion in Excel geht es recht einfach.

Spring Specials 2024: Word & Excel Templates

Special Offers 2023: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones

Popular Posts:

2306, 2023

With the webcam to your dream job: Preparation for the video job interview!

June 23rd, 2023|Categories: Career, Hardware, Homeoffice, Shorts & Tutorials|Tags: , , |

Streamline your video interview with our tips on technique, setting, dress, body language and preparation. It's not quite success from the sofa, but this is where you can prove your professionalism.

1805, 2023

IT security: The deceptive appearance of digital security

May 18th, 2023|Categories: Data Protection, Hardware, Homeoffice, Software|Tags: , |

In a digitized world, IT security is an important factor. But technology alone is not enough. A holistic strategy is needed that also takes into account the human component.

2104, 2023

Create Excel Budget Book – with Statistics – Tutorial

April 21st, 2023|Categories: Internet, Finance & Shopping, Microsoft Excel, Microsoft Office, Office 365, Shorts & Tutorials|Tags: , , , |

Create your own Excel budget book with a graphical dashboard, statistics, trends and data cut-off. A lot is possible with pivot tables and pivot charts.

1504, 2023

Excel Database with Input Form and Search Function

April 15th, 2023|Categories: Microsoft Excel, Microsoft Office, Office 365, Shorts & Tutorials|Tags: , |

So erstellen Sie eine Datenbank mit Eingabemaske und Suchfunktion OHNE VBA KENNTNISSE in Excel ganz einfach. Durch eine gut versteckte Funktion in Excel geht es recht einfach.

Spring Specials 2024: Word & Excel Templates

Special Offers 2023: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones

Title

Ads

Popular Posts:

Search by category:

Autumn Specials:

Anzeige
Go to Top