Általános képlet
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Összegzés
Két lista összehasonlításához és a hiányzó értékek egyik listáról a másikhoz való húzásához használhatja az INDEX és a MATCH alapú tömbképletet. A bemutatott példában a B lista utolsó értéke a D11 cellában található. A D12 képlete, lemásolva:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
ahol "teljes" a B5: B15 megnevezett tartomány.
Megjegyzés: ez egy tömbképlet, amelyet a control + shift + enter billentyűkombinációval kell megadni.
Magyarázat
Belülről kifelé haladva ennek a képletnek a lényege a belső MATCH kifejezés:
ISNA(MATCH(complete,$D$5:D11,0)
Itt a MATCH függvényt használják az összes "teljes" érték összehasonlítására a részlistával. A "teljes" nevű tartományt használja a keresési értékekhez, a részleges listát pedig a keresési tömbként. Figyeljük meg azonban, hogy a részleges lista egy táguló tartományként kerül megadásra, amely a képletcella fölött egy cellával végződik. Ez lehetővé teszi a részleges lista kibővítését, hogy új értékeket tartalmazzon, amint azok az eredeti lista alatt megjelennek.
A MATCH eredménye egy tömb szám és # N / A hiba, ahol a számok a teljes listában a részlistában létező értékeket jelentik; és a hibák a hiányzó értékeket jelentik:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
Az ISNA függvény segítségével ezeket az eredményeket IGAZ és HAMIS értékek tömbjévé alakítják át. Ebben a tömbben a TRUE hiányzó értékeknek, a FALSE pedig a meglévő értékeknek felel meg:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
Az ISNA függvény ezt a tömböt külsõ tömbként adja vissza a külsõ MATCH-hoz. A MATCH függvény mindig az első talált találatot adja vissza, így az egyezés visszaadja az első hiányzó érték pozícióját (sorát). Ez az eredmény sorszámként kerül az INDEX-be, tömbként megadva a "teljes" nevű tartományt.
A D12 cellában az első hiányzó érték a "kiwi" a 2. sorban, tehát:
=INDEX(complete,2) // returns "kiwi"
A D13-ban a "kiwi" szerepel a bővülő referenciában, így az első hiányzó érték a "körte":
=INDEX(complete,5) // returns "pear"
Stb. Miután az összes hiányzó értéket hozzáadta, a képlet visszaadja a # N / A hibát.
