Formula generică
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
rezumat
Pentru a compara două liste și a trage valorile lipsă dintr-o listă în cealaltă, puteți utiliza o formulă matrice bazată pe INDEX și MATCH. În exemplul prezentat, ultima valoare din lista B se află în celula D11. Formula din D12, copiată, este:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
unde „complet” este gama numită B5: B15.
Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter.
Explicaţie
Lucrând din interior spre exterior, nucleul acestei formule este expresia MATCH interioară:
ISNA(MATCH(complete,$D$5:D11,0)
Aici, funcția MATCH este utilizată pentru a compara toate valorile „complete” cu lista parțială. Intervalul numit „complet” este utilizat pentru valorile de căutare, iar lista parțială este utilizată ca matrice de căutare. Observați, totuși, că lista parțială este introdusă ca un interval de expansiune care se termină „cu o celulă deasupra” celulei cu formulă. Acest lucru permite listei parțiale să se extindă pentru a include noi valori așa cum apar sub lista originală.
Rezultatul MATCH este o serie de numere și erori # N / A, unde numerele reprezintă valori din lista completă care există în lista parțială; iar erorile reprezintă valori lipsă:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
Funcția ISNA este utilizată pentru a converti aceste rezultate într-o serie de valori ADEVĂRATE și FALSE. În această matrice, TRUE corespunde valorilor lipsă și FALSE corespunde valorilor existente:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
Funcția ISNA returnează această matrice la MATCH-ul exterior ca matrice de căutare. Funcția MATCH returnează întotdeauna primul meci găsit, astfel încât meciul va returna poziția (rândul) primei valori lipsă găsite. Acest rezultat este returnat la INDEX ca număr de rând, cu intervalul numit „complet” furnizat ca matrice.
În celula D12, prima valoare lipsă găsită este „kiwi” la rândul 2, deci avem:
=INDEX(complete,2) // returns "kiwi"
În D13, „kiwi” este acum inclus în referința de extindere, deci prima valoare lipsă este „pere”:
=INDEX(complete,5) // returns "pear"
Și așa mai departe. Odată adăugate toate valorile lipsă, formula va returna eroarea # N / A.
