Hatten Sie jemals eine große Tabelle mit Daten in Excel und benötigen eine einfache Möglichkeit, bestimmte Informationen daraus zu filtern und zu extrahieren? Wenn Sie lernen, SVERWEIS^(VLOOKUP) in Excel zu verwenden , können Sie diese Suche mit nur einer einzigen, leistungsstarken Excel - Funktion durchführen.

Die VLOOKUP- Funktion in Excel macht vielen Leuten Angst, weil sie viele Parameter hat und es mehrere Möglichkeiten gibt, sie zu verwenden. In diesem Artikel erfahren Sie, wie Sie SVERWEIS^(VLOOKUP) in Excel verwenden können und warum die Funktion so leistungsfähig ist.

SVERWEIS-Parameter in Excel^{(VLOOKUP Parameters In Excel)}

Wenn Sie mit der Eingabe =VLOOKUP( in eine beliebige Zelle in Excel beginnen , sehen Sie ein Popup-Fenster mit allen verfügbaren Funktionsparametern.

Lassen Sie uns jeden dieser Parameter untersuchen und was sie bedeuten.

• lookup_value : Der gesuchte Wert aus der Tabelle
• table_array : Der Zellbereich im Blatt, den Sie durchsuchen möchten
• col_index_num : Die Spalte, aus der Sie Ihr Ergebnis ziehen möchten
• [range_lookup] : Übereinstimmungsmodus ( TRUE = ungefähr, FALSE = genau)

Mit diesen vier Parametern können Sie viele verschiedene, nützliche Suchen nach Daten in sehr großen Datensätzen durchführen.

Ein einfaches SVERWEIS-Excel-Beispiel^{(A Simple VLOOKUP Excel Example)}

SVERWEIS ist keine der grundlegenden Excel-Funktionen^{(the basic Excel functions)} , die Sie vielleicht gelernt haben, also schauen wir uns ein einfaches Beispiel an, um loszulegen.

Für das folgende Beispiel verwenden wir eine große Tabelle mit SAT -Ergebnissen für Schulen in den Vereinigten ^(United) Staaten^(States) . Diese Tabelle enthält über 450 Schulen zusammen mit individuellen SAT- Ergebnissen für Lesen, Mathematik und Schreiben. Fühlen Sie^(Feel) sich frei, herunterzuladen, um mitzumachen. Es gibt eine externe Verbindung, die die Daten abruft, sodass Sie beim Öffnen der Datei eine Warnung erhalten, aber es ist sicher.

Es wäre sehr zeitaufwändig, einen so großen Datensatz zu durchsuchen, um die Schule zu finden, an der Sie interessiert sind.

Stattdessen können Sie in den leeren Zellen an der Seite der Tabelle ein einfaches Formular erstellen. Um diese Suche durchzuführen, erstellen Sie einfach ein Feld für Schule^(School) und drei zusätzliche Felder für Lesen, Rechnen und Schreiben von Partituren.

Als Nächstes müssen Sie die VLOOKUP- Funktion in Excel verwenden , damit diese drei Felder funktionieren. Erstellen Sie im Feld „Lesen“ die SVERWEIS - ^(Reading)Funktion^(VLOOKUP) wie folgt:

1. Geben Sie = SVERWEIS =VLOOKUP(
2. Wählen Sie das Feld Schule^(School) aus, in diesem Beispiel I2 . Geben Sie ein Komma ein.
3. Wählen Sie den gesamten Zellbereich aus, der die Daten enthält, die Sie nachschlagen möchten. Geben Sie ein Komma ein.

Wenn Sie den Bereich auswählen, können Sie mit der Spalte beginnen, die Sie zum Nachschlagen verwenden (in diesem Fall die Spalte mit dem Schulnamen), und dann alle anderen Spalten und Zeilen auswählen, die die Daten enthalten.

Hinweis^(Note) : Die SVERWEIS-^(VLOOKUP) Funktion in Excel kann nur Zellen rechts von der Suchspalte durchsuchen. In diesem Beispiel muss sich die Spalte mit dem Schulnamen links von den gesuchten Daten befinden.

1. Als Nächstes müssen Sie zum Abrufen der Lesepunktzahl^(Reading) die 3. Spalte der am weitesten links ausgewählten Spalte auswählen. Geben Sie also eine 3 und dann ein weiteres Komma ein.
2. Geben Sie abschließend FALSE für eine exakte Übereinstimmung ein und schließen Sie die Funktion mit a ) .

Ihre endgültige SVERWEIS-^(VLOOKUP) Funktion sollte in etwa so aussehen:

=VLOOKUP(I2,B2:G461,3,FALSE)

Wenn Sie zum ersten Mal die Eingabetaste^(Enter) drücken und die Funktion beenden, werden Sie feststellen, dass das Feld Reading ein ^(Reading)#N/A enthält .

Dies liegt daran, dass das Feld Schule^(School) leer ist und die SVERWEIS-^(VLOOKUP) Funktion nichts finden kann. Wenn Sie jedoch den Namen einer weiterführenden Schule eingeben, die Sie nachschlagen möchten, sehen Sie die korrekten Ergebnisse aus dieser Zeile für die Lesekompetenz^(Reading) .

Wie man damit umgeht, dass SVERWEIS Groß- und Kleinschreibung beachtet^{(How To Deal With VLOOKUP Being Case- Sensitive)}

Möglicherweise stellen Sie fest, dass Sie keine Ergebnisse sehen, wenn Sie den Namen der Schule nicht in der gleichen Schreibweise wie im Datensatz eingeben.

Dies liegt daran, dass die SVERWEIS-^(VLOOKUP) Funktion zwischen Groß- und Kleinschreibung unterscheidet. Dies kann ärgerlich sein, insbesondere bei einem sehr großen Datensatz, bei dem die Spalte, die Sie durchsuchen, nicht mit der Großschreibung übereinstimmt.

Um dies zu umgehen, können Sie erzwingen, dass das, wonach Sie suchen, auf Kleinbuchstaben umgestellt wird, bevor Sie die Ergebnisse nachschlagen. Erstellen Sie dazu eine neue Spalte neben der gesuchten Spalte. Geben Sie die Funktion ein:

=TRIM(LOWER(B2))

Dadurch wird der Schulname kleingeschrieben und alle überflüssigen Zeichen (Leerzeichen) entfernt, die sich möglicherweise auf der linken oder rechten Seite des Namens befinden.

Halten Sie die Umschalttaste^(Shift) gedrückt und platzieren Sie den Mauszeiger über der unteren rechten Ecke der ersten Zelle, bis er sich in zwei horizontale Linien ändert. Doppelklicken^(Double) Sie mit der Maus, um die gesamte Spalte automatisch auszufüllen.

Da SVERWEIS^(VLOOKUP) versucht, die Formel statt des Textes in diesen Zellen zu verwenden, müssen Sie schließlich alle nur in Werte umwandeln. Kopieren Sie dazu die gesamte Spalte, klicken Sie mit der rechten Maustaste in die erste Zelle und fügen Sie nur Werte ein.

Nachdem alle Ihre Daten in dieser neuen Spalte bereinigt sind, ändern Sie Ihre SVERWEIS-^(VLOOKUP) Funktion in Excel leicht , um diese neue Spalte anstelle der vorherigen zu verwenden, indem Sie den Nachschlagebereich^{(the lookup range)} bei C2 anstelle von B2 beginnen.

=VLOOKUP(I2,C2:G461,3,FALSE)

Jetzt werden Sie feststellen, dass Sie immer ein gutes Suchergebnis erhalten, wenn Sie Ihre Suche immer in Kleinbuchstaben eingeben.

Dies ist ein praktischer Excel-Tipp^{(handy Excel tip)} , um die Tatsache zu überwinden, dass bei SVERWEIS zwischen^(VLOOKUP) Groß- und Kleinschreibung unterschieden wird.

SVERWEIS Ungefähre Übereinstimmung

Während das im ersten Abschnitt dieses Artikels beschriebene LOOKUP- Beispiel für exakte Übereinstimmung ziemlich einfach ist, ist die ungefähre Übereinstimmung etwas komplexer.

Die ungefähre Übereinstimmung wird am besten verwendet, um Zahlenbereiche zu durchsuchen. Dazu muss der Suchbereich richtig sortiert sein. Das beste Beispiel dafür ist eine VLOOKUP- Funktion, um nach einer Buchstabennote zu suchen, die einer Zahlennote entspricht.

Wenn ein Lehrer eine lange Liste von Hausaufgabennoten von Schülern aus dem ganzen Jahr mit einer abschließenden gemittelten Spalte hat, wäre es schön, wenn die dieser Abschlussnote entsprechende Buchstabennote automatisch angezeigt würde.

Dies ist mit der SVERWEIS-^(VLOOKUP) Funktion möglich. Alles, was erforderlich ist, ist eine Nachschlagetabelle auf der rechten Seite, die die entsprechende Buchstabennote für jeden numerischen Bewertungsbereich enthält.

Jetzt können Sie mit der VLOOKUP- Funktion und einer ungefähren Übereinstimmung die richtige Buchstabennote finden, die dem richtigen numerischen Bereich entspricht.

In dieser SVERWEIS-Funktion:

• lookup_value : F2, die endgültige Durchschnittsnote
• table_array : I2:J8, Der Nachschlagebereich für die Buchstabenqualität
• index_column : 2, die zweite Spalte in der Nachschlagetabelle
• [range_lookup] : TRUE, ungefähre Übereinstimmung

Nachdem Sie die SVERWEIS-^(VLOOKUP) Funktion in G2 beendet und die Eingabetaste^(Enter) gedrückt haben, können Sie die restlichen Zellen mit dem gleichen Ansatz wie im letzten Abschnitt beschrieben ausfüllen. Sie sehen alle Buchstabennoten richtig ausgefüllt.

Beachten Sie, dass die SVERWEIS-^(VLOOKUP) Funktion in Excel vom unteren Ende des Notenbereichs mit der zugewiesenen Buchstabenpunktzahl bis zum oberen Ende des Bereichs der nächsten Buchstabenpunktzahl sucht.

Also muss „C“ der Buchstabe sein, der dem unteren Bereich (75) zugewiesen ist, und B muss dem unteren (Minimum) seines eigenen Buchstabenbereichs zugewiesen werden. SVERWEIS^(VLOOKUP) „findet“ das Ergebnis für 60 (D) als den nächsten ungefähren Wert für irgendetwas zwischen 60 und 75.

SVERWEIS^(VLOOKUP) in Excel ist eine sehr mächtige Funktion, die es schon lange gibt. Es ist auch nützlich , um übereinstimmende Werte irgendwo in einer Excel-Arbeitsmappe zu finden^{(finding matching values anywhere in an Excel workbook)} .

Beachten Sie jedoch, dass Microsoft - Benutzer mit einem monatlichen Office 365 -Abonnement jetzt Zugriff auf eine neuere XLOOKUP-Funktion haben. Diese Funktion hat mehr Parameter und zusätzliche Flexibilität. Benutzer mit einem halbjährlichen Abonnement müssen warten, bis das Update im Juli 2020 eingeführt wird^{(July 2020)} .

How to Use VLOOKUP in Excel

Have you ever had a large spreadsheet with data in Εxcel and need an easy way to filter and extract specific information from it? If you learn hоw to use VLОOKUP in Excel, you can do this lookup with just a single, powerful Excel funсtіon.

The VLOOKUP function in Excel scares a lot of people because it has a lot of parameters and there are multiple ways to use it. In this article you’ll learn all of the ways you can use VLOOKUP in Excel and why the function is so powerful.

VLOOKUP Parameters In Excel

When you start typing =VLOOKUP( into any cell in Excel, you’ll see a pop-up showing all of the available function parameters.

Let’s examine each of these parameters and what they mean.

• lookup_value: The value you’re looking for from the spreadsheet
• table_array: The range of cells in the sheet you want to search through
• col_index_num: The column where you want to pull your result from
• [range_lookup]: Match mode (TRUE = approximate, FALSE = exact)

These four parameters let you do a lot of different, useful searches for data inside of very large datasets.

A Simple VLOOKUP Excel Example

VLOOKUP isn’t one of the basic Excel functions you might have learned, so let’s look at a simple example to get started.

For the following example, we’ll use a large spreadsheet of SAT scores for schools in the United States. This spreadsheet contains over 450 schools along with individual SAT scores for reading, math, and writing. Feel free to download to follow along. There is an external connection that pulls the data, so you’ll get a warning when opening the file, but it’s safe.

It would be very time consuming to search through such a large dataset to find the school that you’re interested in.

Instead, you can create a simple form in the blank cells on the side of the table. To conduct this search, just make one field for School, and three additional fields for reading, math, and writing scores.

Next, you’ll need to use the VLOOKUP function in Excel to make these three fields work. In the Reading field, create the VLOOKUP function as follows:

1. Type =VLOOKUP(
2. Select the School field, which in this example is I2. Type a comma.
3. Select the entire range of cells that contain the data you want to look up. Type a comma.

When you select the range, you can start from the column you’re using to look up (in this case the school name column), and then select all of the other columns and rows that contain the data.

Note: The VLOOKUP function in Excel can only search through cells to the right of the search column. In this example, the school name column needs to be to the left of the data you’re looking up.

1. Next, to retrieve the Reading score, you’ll need to select the 3rd column from the leftmost selected column. So, type a 3 and then type another comma.
2. Finally, type FALSE for an exact match, and close the function with a ).

Your final VLOOKUP function should look something like this:

=VLOOKUP(I2,B2:G461,3,FALSE)

When you first press Enter and finish the function, you’ll notice the Reading field will contain an #N/A.

This is because the School field is blank and there is nothing for the VLOOKUP function to find. However, if you enter the name of any high school you want to look up, you’ll see the correct results from that row for the Reading score.

How To Deal With VLOOKUP Being Case- Sensitive

You may notice that if you don’t type the name of the school in the same case as how it’s listed in the dataset, you will not see any results.

This is because the VLOOKUP function is case sensitive. This can be annoying, especially for a very large dataset where the column you’re searching through is inconsistent with how things are capitalized.

To get around this, you can force what you’re searching for to switch to lowercase before looking up the results. To do this, create a new column next to the column you’re searching. Type the function:

=TRIM(LOWER(B2))

This will lowercase the school name and remove any extraneous characters (spaces) that might be on the left or right side of the name.

Hold down the Shift key and place the mouse cursor over the lower right corner of the first cell until it changes to two horizontal lines. Double click the mouse to autofill the entire column.

Finally, since VLOOKUP will try to use the formula rather than the text in these cells, you need to convert them all to values only.  To do this, copy the entire column, right click in the first cell, and paste values only.

Now that all of your data is cleaned up in this new column, slightly modify your VLOOKUP function in Excel to use this new column instead of the previous one by starting the lookup range at C2 instead of B2.

=VLOOKUP(I2,C2:G461,3,FALSE)

Now you’ll notice that if you always type your search in lower case, you’ll always get a good search result.

This is a handy Excel tip to overcome the fact that VLOOKUP is case sensitive.

VLOOKUP Approximate Match

While the exact match LOOKUP example described in the first section of this article is pretty straightforward, the approximate match is a little more complex.

The approximate match is best used to search through number ranges. To do this correctly the search range needs to be properly sorted. The best example of this is a VLOOKUP function to search for a letter grade that corresponds to a number grade.

If a teacher has a long list of student homework grades from throughout the year with a final averaged column, it would be nice to have the letter grade corresponding to that final grade come up automatically.

This is possible with the VLOOKUP function. All that’s required is a lookup table off to the right that contains the appropriate letter grade for each numerical score range.

Now, using the VLOOKUP function and an approximate match, you can find the proper letter grade corresponding to the correct numeric range.

In this VLOOKUP function:

• lookup_value: F2, the final averaged grade
• table_array: I2:J8, The letter grade lookup range
• index_column: 2, the second column in the lookup table
• [range_lookup]: TRUE, approximate match

Once you finish the VLOOKUP function in G2 and press Enter, you can fill in the rest of the cells using the same approach described in the last section. You’ll see all of the letter grades properly filled in.

Note that the VLOOKUP function in Excel searches from the bottom end of the grade range with the assigned letter score to the top of the range of the next letter score.

So, “C” needs to be the letter assigned to the lower range (75), and B is assigned to the bottom (minimum) of its own letter range. VLOOKUP will “find” the result for 60 (D) as the closest approximate value for anything between 60 to 75.

VLOOKUP in Excel is a very powerful function that has been available for a long time. It is also useful for finding matching values anywhere in an Excel workbook.

Keep in mind, however, that Microsoft users who have a monthly Office 365 subscription now have access to a newer XLOOKUP function. This function has more parameters and additional flexibility. Users with a semi-annual subscription will need to wait for the update to roll out in July 2020.

Related posts

• Hinzufügen einer linearen Regressions-Trendlinie zu einem Excel-Streudiagramm

• So erstellen Sie ein Histogramm in Excel

• So schützen Sie eine Excel-Datei sicher mit einem Kennwort

• So erstellen Sie ein Flussdiagramm in Word und Excel

• So erstellen Sie Etiketten in Word aus einer Excel-Tabelle

• So erstellen Sie eine Grußkarte mit MS Word

• Verwenden Sie Zusammenfassungsfunktionen, um Daten in Excel zusammenzufassen

• So subtrahieren Sie Daten in Excel

• 13 OneNote-Tipps und Tricks zur besseren Organisation Ihrer Notizen

• So vergleichen Sie zwei Excel-Dateien und heben die Unterschiede hervor

• So öffnen Sie mehrere Instanzen von Excel

• So verwenden Sie die YEARFRAC-Funktion in Excel

• So erstellen Sie eine Verteilerliste in Outlook

• Verknüpfen Sie Zellen zwischen Blättern und Arbeitsmappen in Excel

• So fügen Sie einer Excel-Arbeitsblattzelle Kommentare hinzu

• Verwenden Sie das Excel-Überwachungsfenster, um wichtige Zellen in einer Arbeitsmappe zu überwachen

• Verwenden Sie Excel, um einen effektiven Zinssatz aus einem nominalen Zinssatz zu berechnen

• So filtern Sie Daten in Excel

• So verwenden Sie COUNTIFS, SUMIFS, AVERAGEIFS in Excel

• So finden und berechnen Sie den Bereich in Excel