Generisk formel
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Sammanfattning
För att jämföra två listor och dra saknade värden från en lista till en annan kan du använda en matrisformel baserad på INDEX och MATCH. I det visade exemplet är det sista värdet i lista B i cell D11. Formeln i D12, kopierad ner, är:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
där "komplett" är det namngivna området B5: B15.
Obs: detta är en matrisformel och måste anges med kontroll + skift + enter.
Förklaring
Arbetar inifrån och ut, kärnan i denna formel är det inre MATCH-uttrycket:
ISNA(MATCH(complete,$D$5:D11,0)
Här används MATCH-funktionen för att jämföra alla "kompletta" värden mot den partiella listan. Det angivna intervallet "komplett" används för uppslagsvärden, och den partiella listan används som uppslagsmatris. Observera dock att den partiella listan anges som ett expanderande intervall som slutar "en cell ovanför" formelcellen. Detta gör att den partiella listan kan utvidgas till att inkludera nya värden när de visas under den ursprungliga listan.
Resultatet av MATCH är en rad med siffror och # N / A-fel, där siffror representerar värden i hela listan som finns i den partiella listan; och fel representerar saknade värden:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
ISNA-funktionen används för att konvertera dessa resultat till en matris med SANT och FALSKA värden. I den här matrisen motsvarar TRUE saknade värden och FALSE motsvarar befintliga värden:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
ISNA-funktionen returnerar denna matris till den yttre MATCH som uppslagsmatris. MATCH-funktionen returnerar alltid den första matchningen som hittats, så matchningen returnerar positionen (raden) för det första hittade värdet. Detta resultat returneras till INDEX som radnummer, med det angivna intervallet "komplett" som matris.
I cell D12 är det första saknade värdet "kiwi" på rad 2, så vi har:
=INDEX(complete,2) // returns "kiwi"
I D13 ingår nu "kiwi" i den expanderande referensen, så det första saknade värdet är "päron":
=INDEX(complete,5) // returns "pear"
Och så vidare. När alla värden som saknas har lagts till returnerar formeln # N / A-felet.
