Ich habe versucht, einen Weg zu finden, die Bewertungsspalte (Bild unten) auszufüllen, ohne mich in eine lange Formel vertiefen zu müssen. Ich habe es VLOOKUPmit „INDEX /MATCH“ versucht, bin aber in eine Sackgasse geraten.
Normalerweise VLOOKUPgibt es maximal 2 Spalten für das Tabellenarray, also G2:J3 basierend auf meinem Beispiel. Aber ich wollte das Tabellenarray basierend auf dem Jahr (G3:G5) auswählen. Gibt es eine Möglichkeit, dies zu tun?
Jede Hilfe ist willkommen!
Antwort1
Versuchen Sie diese Formel:
=IF(OR(D3>VLOOKUP(B3,G:J,2,FALSE),D3=VLOOKUP(B3,G:J,2,FALSE)),$H$2,IF(AND(D3<VLOOKUP(B3,G:J,2,FALSE),OR(D3>VLOOKUP(B3,G:J,3,FALSE),D3=VLOOKUP(B3,G:J,3,FALSE))),$I$2,$J$2))
Antwort2
Ihre Definitionen sind möglicherweise nicht gut durchdacht. Sie haben Bereiche, die wie Mindestgrenzwerte aussehen (die Punktzahl muss >= sein, um sich für diese Bewertung zu qualifizieren). Es gibt jedoch Punktzahlen unter „Schlecht“ ohne Bewertungsnamen. Wenn die Grenzwerte für jede Bewertung ein Maximum sind, gibt es keine Bewertung, die besser ist als der Grenzwert „Gut“. Wenn die Grenzwerte gemischt sind (hoch für Schlecht, niedrig für Gut), würde dies den Durchschnitt definieren. Wie verwenden Sie also den Durchschnittsgrenzwert?
Hier ist ein Lösungsansatz, den Sie ändern können, wenn Sie die Bereiche neu definieren. Er basiert darauf, dass die Grenzwerte für jede Bewertung Mindestwerte sind. Dies erfordert, dass die Bewertungstabelle aufsteigend sortiert ist. Er ist skalierbar, da Sie nur den Bereich der Nachschlagetabelle anpassen müssen; es ist keine separate Nachschlageformel für jedes Jahr erforderlich:
Werte, die nicht durch Ihre Bewertungsgrenzwerte definiert sind, geben einen Fehler zurück. Es war nicht klar, wie Sie das Problem beheben möchten. Die Formel in E3:
=INDEX($H$2:$J$2,,MATCH(D3,INDIRECT("$H$"&MATCH(B3,$G$1:$G$5,0)&":$J$"&MATCH(B3,$G$1:$G$5,0))))
INDEX gibt den Rating-Namen basierend auf dem MATCH-Ergebnis zurück. MATCH vergleicht die Punktzahl mit den Rating-Grenzwerten.
Es bestimmt, welche Zeile der Nachschlagetabelle verwendet werden soll, indem das Jahr abgeglichen wird. INDIREKT erstellt aus diesem Ergebnis den richtigen Nachschlagebereich.