For at hente flere matchende værdier fra et sæt data med en formel kan du bruge IF- og SMALL -funktionerne til at finde ud af rækkenummeret for hver kamp og feed denne værdi tilbage til INDEX. I det viste eksempel er formlen i I7:
{= INDEX (array, SMALL ( IF (vals=val, ROW (vals)- ROW ( INDEX (vals,1,1))+1),nth))}
Hvor navngivne områder er amts (D4: D11), id (I3) og id'er (C4: C11).
Bemærk, at dette er en matrixformel og skal indtastes med Control + Shift + Enter.
Forklaring
I kernen er denne formel simpelthen en INDEX -formel, der henter værdien i et array på en given position. Værdien for n er angivet i kolonne H, og alt det 'tunge' arbejde, formlen gør, er at finde ud af rækken, hvorfra en værdi skal hentes, hvor rækken svarer til 'nth' match.
IF -funktionen udfører arbejdet med at finde ud af, hvilke rækker der indeholder et match, og funktionen SMALL returnerer den nte værdi fra listen. Inde i IF er den logiske test:
{= INDEX (amts, SMALL ( IF (ids=id, ROW (ids)- ROW ( INDEX (ids,1,1))+1),H6))}
som giver denne matrix:
hvornår skal man bruge vlookup i Excel
ids=id
Bemærk kunde -id -matchene på 1. og 4. position, som vises som SAND. Argumentet 'værdi hvis sandt' i IF genererer en liste over relative rækkenumre med dette udtryk:
{TRUEFALSEFALSETRUEFALSEFALSEFALSE}
som producerer denne matrix:
ROW (ids)- ROW ( INDEX (ids,1,1))+1
Denne matrix 'filtreres' derefter af de logiske testresultater, og IF -funktionen returnerer følgende array -resultat:
{1234567}
Bemærk, vi har gyldige rækkenumre for række 1 og række 2.
Denne matrix behandles derefter af SMALL, som er konfigureret til at bruge værdier i kolonne H til at returnere 'nth' værdier. SMALL -funktionen ignorerer automatisk de logiske værdier SAND og FALSK i matrixen. I sidste ende reducerer formlerne til:
{1FALSEFALSE4FALSEFALSEFALSE}
Håndteringsfejl
Når der ikke er flere matches for et givet id, returnerer SMALL -funktionen en #NUM fejl. Du kan håndtere denne fejl med IFERROR -funktion eller ved at tilføje logik til at tælle tændstikker og afbryde behandlingen, når tallet i kolonne H er større end tællingen. Det eksempel her viser en tilgang.
Flere kriterier
For at tilføje flere kriterier bruger du boolsk logik , som forklaret i dette eksempel .
Forfatter Dave Bruns