Contains paid promotion

Advertisement

So you get targeted records by S-reference and
W-reference out of 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 to get a better overview.

So what to do?

Windows Lizenz deaktivieren / umziehen

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 functions S-reference and W-reference come into play.

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

Advertisement
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1,Grau, Rot
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC ...*
by SanDisk

Kapazität: 64 GB, Flash Card Typ: MicroSDXC, Flash-Memory-K...

List Price: € 30.99 You Save: € 11.99 (39%) Prime Price: € 19.00 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

So you get targeted records by S-reference and W-reference out of 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 to get a better overview.

So what to do?

Windows Lizenz deaktivieren / umziehen

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 functions S-reference and W-reference come into play.

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

Advertisement
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1,Grau, Rot
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC ...*
by SanDisk

Kapazität: 64 GB, Flash Card Typ: MicroSDXC, Flash-Memory-K...

List Price: € 30.99 You Save: € 11.99 (39%) Prime Price: € 19.00 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

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 the 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)

Vertical search direction
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)

Horizontal search direction
Advertisement

In general we first clarify the definition of the S-reference and the 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:

Vertical search direction
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:

Horizontal search direction
Advertisement

2. Procedure S-reference

2. Procedure S-reference

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

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

In the invoice later after entering the customer number, the recipient address should be filled automatically.

see picture: (click to enlarge)

Sample S-reference
Sample S-reference

For the automated filling of the receiver head to work, we now have 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:

=SVERWEIS(

  • 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 later enter our customer number.

What does this mean Excel will search for exactly what is entered there in the data source.

see picture: (click to enlarge)

Search Criterion set
Advertisement
#1
Fitbit Versa Health & Fitness Smartwatch
Fitbit Versa Health & Fitness Smartwatch*
by FITET|#Fitbit

FitBit VERSA. Leichte und wasserfeste Smartwatch, mit der Ge...

Price not available zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 13:32 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

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 set
Advertisement

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 here we are asking 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 that is 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-reference-finished formula
Advertisement
#1
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis zu 100MB/Sek
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis ...*
by SanDisk

USB 3.0 DRIVE, SANDISK,ULTRA CRUZER,64GB--- Memory Capacity ...

List Price: € 26.99 You Save: € 11.00 (41%) Prime Price: € 15.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

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

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

In the invoice later after entering the customer number, the recipient address should be filled automatically.

see picture:

Sample S-reference
Sample S-reference

For the automated filling of the receiver head to work, we now have 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:

=SVERWEIS(

  • 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 later enter our customer number.

What does this mean Excel will search for exactly what is entered there in the data source.

see picture:

Search Criterion set
Advertisement
#1
Fitbit Versa Health & Fitness Smartwatch
Fitbit Versa Health & Fitness Smartwatch*
by FITET|#Fitbit

FitBit VERSA. Leichte und wasserfeste Smartwatch, mit der Ge...

Price not available zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 13:32 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

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 set
Advertisement

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 here we are asking 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 that is similar.
So we enter a “0“.

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

see picture:

S-reference-finished formula
Advertisement
#1
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis zu 100MB/Sek
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis ...*
by SanDisk

USB 3.0 DRIVE, SANDISK,ULTRA CRUZER,64GB--- Memory Capacity ...

List Price: € 26.99 You Save: € 11.00 (41%) Prime Price: € 15.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

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 swapped) to represent the W-reference

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

=WVERWEIS(

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 just a row index instead of a column 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-reference-finished formula
Advertisement
#1
WD Elements Portable, Externe Festplatte - 2 TB - USB 3.0 - WDBU6Y0020BBK-WESN
WD Elements Portable, Externe Festplatte - 2 TB - ...*
by Western Digital

Festplattenkapazität: 2000 GB, Festplatten-Formfaktor: 2.5 ...

*Zuletzt aktualisiert am 17. August 2018 um 12:51 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

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 even in the basic version, some work can be done well with these fairly simple functions.

Print Friendly, PDF & Email

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 swapped) to represent the W-reference

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

=WVERWEIS(

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 just a row index instead of a column 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:

W-reference-finished formula
Advertisement
#1
WD Elements Portable, Externe Festplatte - 2 TB - USB 3.0 - WDBU6Y0020BBK-WESN
WD Elements Portable, Externe Festplatte - 2 TB - ...*
by Western Digital

Festplattenkapazität: 2000 GB, Festplatten-Formfaktor: 2.5 ...

*Zuletzt aktualisiert am 17. August 2018 um 12:51 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

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 even in the basic version, some work can be done well with these fairly simple functions.

Print Friendly, PDF & Email
Become Guest Writer

We publish your guest contribution free of charge in German and English
incl. backlink to your website and identification of the author

Anzeige
Blogverzeichnis Bloggerei.de

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Advertisement
#1
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterwasser Action Kamera 40M Digitale Videokamera mit 170 ° Weitwinkelobjektiv Eis Sony Sensor, Zwei verbesserten Batterien, Transportskoffer und 24 Multiples
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterw...*
by Apeman

Das ist unser Leben,verpasse nie wieder einen wunderbaren Mo...

List Price: € 109.99 You Save: € 20.00 (18%) Prime Price: € 89.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 15:32 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Advertisement
#1
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterwasser Action Kamera 40M Digitale Videokamera mit 170 ° Weitwinkelobjektiv Eis Sony Sensor, Zwei verbesserten Batterien, Transportskoffer und 24 Multiples
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterw...*
by Apeman

Das ist unser Leben,verpasse nie wieder einen wunderbaren Mo...

List Price: € 109.99 You Save: € 20.00 (18%) Prime Price: € 89.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 15:32 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.
2018-08-09T11:39:14+00:00By |Categories: Excel 2016, Office 2016|
In order to optimize our website for you and to be able to continuously improve it, we use cookies. By continuing to use the website, you agree to the use of cookies. Ok