Office: Verweis in einer Spalte suchen, Wert aus einer anderen Spalte gleiche Zeile einsetzen

Hallo und Guten Abend. War etwas schwierig für mein Problem eine passende Überschrift zu finden, hoffe ich hab es einigermaßen gut getroffen. Und auch die Suchfunktion war nicht hilfreich, daher hab ich ein neues Thema eröffnet.

Es geht darum das ich nun schon seit einigen Tagen versuche mit Hilfe von Google und diversen Excel-Tutorials herauszufinden wie ich:

1. In einem Tabellenblatt nach einem bestimmten Wert in einer von mir bestimmten Spalte suchen kann.
2. Der Wert gefunden wird und dann ein zweiter Wert aus der gleichen Zeile aber einer anderen, von mir bestimmten, Spalte wiedergegeben wird.

Ich habe in der Vergangenheit schon mit der SVERWEIS Funktion gearbeitet, die sich unveränderliche Daten aus einem zweiten Tabellenblatt holt. Also dachte ich hier nun wieder an die VERWEIS-Funktion. Aber alles was ich dazu finden konnte waren Formeln die grundsätzlich nur in Spalte A nach einem Wert suchen und immer nur das Ergebnis aus Spalte B wiedergeben. Ich brauche aber eine Formel die z.B. einen Wert in Spalte B sucht und dann das Ergebnis dazu aus Spalte N wiedergibt.

Hab dann einfach so ein wenig herumprobiert aber immer nur Fehlermeldungen bekommen.

Im Detail geht es um diese Tabelle hier:
Google Spreadsheet

Ich habe die Forenregeln 6.1 und 6.3 gelesen und versichere dass dieser Link weder zu einem Download noch zu einer anderen unangemessenen Seite führt.

• In der 5. Zeile steht in der Spalte B Mehl. (Die Tabelle ist mittlerweile 393 Zeilen groß und der Begriff Mehl kommt mittlerweile sehr häufig vor. Bleiben wir aber ganz oben, das ist einfacher.)
• In Zeile 6 hab ich dann Mehl in der Spalte H mit der Überschrift Zutaten #2 stehen.
• Das Ergebnis in...
  Zeile 6, Spalte J mit der Überschrift Kosten #2
  bezieht sich auf das Ergebnis in...
  Zeile 5, Spalte K mit der Überschrift Kosten Gesamt

Ich hab das Beispiel mal als Tabelle mit hier ins Forum eingefügt:

[table="width: 600, class: grid, align: center"]
[tr]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[td]E[/td]
[td]F[/td]
[td]G[/td]
[td]H[/td]
[td]I[/td]
[td]J[/td]
[td]K[/td]
[/tr]
[tr]
[td]Cash Zutaten?[/td]
[td]Name[/td]
[td]Verkaufspreis[/td]
[td]Kraft[/td]
[td]Zutat #1[/td]
[td]Anzahl[/td]
[td]Kosten #1[/td]
[td]Zutat #2[/td]
[td]Anzahl[/td]
[td]Kosten #2[/td]
[td]Kosten Kosten Gesamt[/td]
[/tr]
[tr]
[td]X[/td]
[td]Mehl[/td]
[td]90[/td]
[td]1[/td]
[td]Weizen[/td]
[td]2[/td]
[td]28[/td]
[td]Weizen[/td]
[td]2[/td]
[td]28[/td]
[td]56[/td]
[/tr]
[tr]
[td]X[/td]
[td]Teig[/td]
[td]290[/td]
[td]2[/td]
[td]Eier[/td]
[td]3[/td]
[td]180[/td]
[td]Mehl[/td]
[td]1[/td]
[td]56[/td]
[td]236[/td]
[/tr]
[/table]

Was ich bisher nun gemacht habe war folgendes: Den Wert für die Eier holt er sich aus dem Extra-Tabellenblatt mit der Formel:

=vlookup(E6;Zutaten!A:B;2;0)*F6

Den Wert für das Mehl habe ich manuell eingegeben:

=(K5*I6)

Aber, wie man sich denken kann, wird das mittlerweile echt aufwendig wenn ich von Zeile 393 ganz nach oben muss um da den Wert des Mehls aus K5 zu übernehmen und dann wieder ganz runter zurück zu meiner Anfangsformel.

Außerdem möchte ich nun eine Sortierfunktion einbauen und die hat mit den manuellen Formeln ein echtes Problem! Wenn ich filter stimmen die Werte nicht mehr und ich kriege überall nur noch Error.

Ein drittes Tabellenblatt kann ich aber auch nicht nehmen da der Wert vom Mehl kein fixer Wert ist sondern sich ändern kann wenn sich etwas im Tabellenblatt Zutaten ändert.

Aber hier kennt doch sicher die korrekte Formel um dieses problem zu lösen, oder? *hundeblick*

 

Hola,

ich hab nicht ins spreadsheet geguckt, aber der Sverweis sollte genau das richtige sein. Falsch ist, dass er nur in Spalte A sucht, da hast du definitiv die falschen Tutorials gelesen:

Sucht den Wertaus A1 in Spalte D und gibt bei Übereinstimmung den Wert aus Spalte G aus, da Spalte G die 4. Spalte in der Suchmatrix ist.

Gruß,
steve1da

 

Hallo steve1da und danke für die Antwort. Aber ich glaube das ist nicht so ganz das was ich gesucht habe, habe mich wahrscheinlich im 1. Post falsch ausgedrückt. Was ich möchte ist:

Er soll in Spalte B nach dem Eintrag "Mehl" suchen, egal ob dieser nun in Zeile 1 oder in Zeile 1000 steht, und wenn er den Wert "Mehl" gefunden hat soll er in Spalte K der gleichen Zeile wo er Mehl gefunden hat (bzw. der 11. Spalte von A aus) den Wert nehmen und in der gewünschten Zelle, im Beispiel oben wäre das J6, einsetzen.

Meine Testformel sah am Ende so aus:

Google-Drive gibt einem sogar eine kleine Hilfestellung zu der Formel:

LOOKUP (Suchschlüssel; Suchbereich|Suchergebisarray; [Ergebnisbereich])

...aber alles was ich am Ende bekomme ist "NAME?"

Ich gehe einfach davon aus dass die Forum grundsätzlich nicht ganz korrekt ist für das was ich für meinen Zweck brauche.

Lookup ist die englische Bezeichnung für Verweis da Google-Drive mit den Deutschen Bezeichnungen Probleme hat.

 

Hola,

die korrekte Formel wäre:

Gruß,
steve1da

 

Also diese Formel funktioniert schonmal, super!

Nun hab ich nen Filter gesetzt und die Spalte M nach A bis Z sortierten lassen und das passt der Formel da oben leider gar nicht! Die sieht jetzt, nach dem sortieren so aus:

Und als Zelleninhalt bekomme ich #N/A

Der Formel muss klar sein dass sie in der gesamten Spalte B5 bis B500 nach dem Begriff "Mehl" suchen soll, nicht nur in B5.

 

Hola,

das mit dem sortieren schau ich mir nachher mal an.

Der Formel ist das schon klar. B5 im Suchbereich definiert nur, ab wo gesucht werden soll!

Gruß,
steve1da

 

Okay, werd ich in meinen grauen Zellen speichern. Ich lass die Sortierfunktion mal im Spreadsheet drin, für den Fall dass du mal reinschauen magst. Wenn du die Sortieroption nicht sehen kannst sag bescheid, hab länger nicht mehr mit Google-Drive gearbeitet und die haben Anfang 2015 auf eine neue Version umgestellt und die Listenansicht von früher rausgenommen, leider. Daher muss ich nun eine richtige einbauen für die User die sich damit nicht auskennen.

Lass dich von den vielen Values und Refs nicht stören. Das liegt daran dass da überall eine manuelle Formel eingetragen ist und er nun die Werte nicht mehr findet. Daher bin ich auf der Suche einer Formel mit der ich dieses Problem umgehen und mir die Arbeit mit der Tabelle erleichtern kann.

Und da es ein freiwilliges unbezahltes Projekt ist hab ich auch keinen Zeitdruck. Also mach dir bitte keinen Streß deswegen. ^_^;

 

Hola,

das mit dem Sortieren kann ich insofern nicht nachvollziehen, als das ich nicht weiß wonach du sortieren willst. Hilft es, wenn du in der Lookup Formel die Suchmatrix absolut setzt?

Gruß,
steve1da

 

Ich weiß nicht ob du schonmal in das Spreadsheet hinein geschaut hast aber ich hab da ja die Spalte mit der Überschrift "Gewinn pro eingesetzter Kraft" und da will ich zum Beispiel nach Maximum bis Minimum sortieren bzw. umgedreht.

Hab die Formel nun an deine alte angepasst:

Und das scheint zu gehen!

Kann ich dich noch was fragen?

Warum in der zweiten Zeile K500? B5 bezieht sich wohl darauf dass er ab Zeile 5 die komplette B Spalte absucht, egal wie lang diese ist, aber warum K500? Es könnte durchaus sein dass die Tabelle eines Tages mehr wie 500 Zeilen haben wird. Oder worauf bezieht sich das 500 genau?

 

Hola,

mit den ganzen #REF Fehlern ist das schwer nachzubauen. Ich würde vorschlagen, die Tabelle so zu lassen und in einer Hilfsspalte/Hilfstabelle die Auswertung nach Max/Min zu machen.

Nein. Es wird ab Zelle B5 die Spalte B bis Zeile 500 durchsucht. Die Suchmatrix ist immer Von-Bis. Es wird nur von B5 bis B500 gesucht und maximal der Ergebnis von K5 bis K500 ausgespuckt. Für mehr Zeilen muss man natürlich den Bereich erhöhen. Komplette Spaltenbereiche wie B:K können das ganze sehr langsam werden lassen, von daher sollte man nur Bereiche verwenden die man unbedingt braucht, z.B. B5:K10000

Gruß,
steve1da