☰

Suche basierend auf zwei Kriterien, einmal einer Zahl und einmal einem Bereich

tag-icon • tag-icon microsoft-excel
Suche basierend auf zwei Kriterien, einmal einer Zahl und einmal einem Bereich

Ich habe zwei Listen, eine mit IDs und Prozentsätzen basierend auf verschiedenen Bereichen und die andere mit IDs und Gesamtwerten. Ich muss den Prozentsatz in der ersten Tabelle anhand der ID und des Gesamtwerts in der zweiten Tabelle nachschlagen.

ID  Bottom Top  Percentage
1   1      50       0.3
1   51     75       0.4
1   76              0.5
2   1               0.75
3   1      25       1
3   26     100      2
3   101    1000     3
3   1001   2000     4
3   2001            5

Second List
ID  Total Amount
1   100
2   146
3   1256

Antwort1

Angenommen, Ihre erste Tabelle befindet sich auf Blatt1 und Ihre zweite Tabelle auf Blatt2 und Sie möchten die Spalte „Betrag“ Ihrer zweiten Tabelle ausfüllen. Mit der folgenden Formel können Sie dies für die erste Zelle „Betrag“ (Zelle C2) von Blatt2 erledigen. Dazu muss die erste Tabelle nach der Spalte „ID“ und dann nach der Spalte „Unterer Wert“ sortiert werden. Diese Formel kann nach der Eingabe kopiert und an das Ende von Tabelle 2 eingefügt werden.

=VLOOKUP(B2, OFFSET(Sheet1!B:D, MATCH(A2, Sheet1!A:A, 0) - 1, 0, COUNTIF(Sheet1!A:A, A2)), 3)

Beachten Sie, dass die oberste Spalte für die Funktion nicht unbedingt erforderlich ist.

Wenn man diese Formel aufschlüsselt, verwendet sie dieVLOOKUPFunktion, um die untere Spalte nach dem nächsten Wert zu durchsuchen, der kleiner oder gleich dem Gesamtwert ist, und gibt dann den Wert in der Spalte „Prozent“ für dieselbe Zeile zurück. Außerdem muss die Suche auf Zeilen beschränkt werden, die der richtigen ID entsprechen, und dies geschieht mit derOFFSETFunktion. DieOFFSETFunktion verwendet die Zeile der ersten übereinstimmenden ID, die mit demMATCHund die Gesamtzahl der übereinstimmenden IDS, die mit derCOUNTIFFunktion.

verwandte Informationen