Excel-formel: Extrahera alla matchningar med hjälpkolumn -

Innehållsförteckning

Generisk formel

=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")

Sammanfattning

Ett sätt att extrahera flera matchningar i Excel är att använda INDEX och MATCH med en hjälpkolumn som flaggar matchande data. Detta undviker komplexiteten i en mer avancerad matrisformel. I exemplet som visas är formeln i H6:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

där ct (G3), data (B3: E52) och hjälpare (E3: E52) heter områden.

Förklaring

Utmaningen med uppslagsformler som hämtar mer än en matchning är att hantera dubbletter (dvs. flera matchningar). Uppslagsformler som VLOOKUP och INDEX + MATCH kan enkelt hitta den första matchningen, men det är mycket svårare att slå upp "alla matchningar" när kriterierna hittar mer än en matchning.

Denna formel hanterar denna utmaning med hjälp av en hjälpkolumn som returnerar ett numeriskt värde som kan användas för att enkelt extrahera flera matchningar. Formeln i hjälpkolumnen ser ut så här:

=SUM(E2,AND(C3=$I$3,D3=$J$3))

Hjälpkolonnen testar varje rad i data för att se om avdelningen i kolumn C matchar värdet i I3 och byggnaden i kolumn D matchar värdet i J3. Båda logiska testerna måste returnera SANT för att AND ska returnera SANT.

För varje rad läggs resultatet från OCH-funktionen till "värdet ovan" i hjälpkolumnen för att generera ett antal. Den praktiska effekten av denna formel är en stegvis räknare som bara ändras när en (ny) matchning hittas. Då förblir värdet detsamma tills nästa matchning hittas. Detta fungerar eftersom SANT / FALSKT resultat returneras med AND tvingas till 1/0-värden som en del av summan. FALSE-resultat lägger till ingenting och Sanna resultat lägger till 1.

Tillbaka i extraktionsområdet ser uppslagsformeln för Namn i kolumn H så här ut:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

Arbetar inifrån och ut, INDEX + MATCH-delen av formeln letar upp namnet på den första matchningen som hittades med radnumret i kolumn G som matchningsvärde:

INDEX(data,MATCH($G6,helper,0),1)

INDEX tar emot alla tre kolumner med data som matrisen (namngivet intervall "data"), och MATCH är konfigurerad för att matcha radnumret inuti hjälpkolumnen (det namngivna intervallet "hjälpare") i exakt matchningsläge (tredje argumentet satt till noll) .

Det är här som skickligheten i formeln blir uppenbar. Hjälpkolumnen innehåller uppenbarligen dubbletter, men det spelar ingen roll, eftersom MATCH bara matchar det första värdet. Enligt design motsvarar varje "första värde" rätt rad i datatabellen.

Formlerna i kolumnerna I och J är desamma som H, förutom kolumnnumret, som i varje fall ökas med en.

IF-satsen som omsluter INDEX / MATCH-formeln utför en enkel funktion - den kontrollerar varje radnummer i extraktionsområdet för att se om radnumret är mindre än eller lika med värdet i G3 (namngivet intervall "ct"), vilket är det totala antalet av alla matchande poster. I så fall körs INDEX / MATCH-logiken. Om inte, matar IF ut en tom sträng ("").

Formeln i G3 (namngivet intervall "ct") är enkel:

=MAX(helper)

Eftersom det maximala värdet i hjälpkolumnen är detsamma som det totala antalet matchningar, är MAX-funktionen allt vi behöver.

Obs! Extraktionsområdet måste konfigureras manuellt för att hantera så mycket data som behövs (dvs. 5 rader, 10 rader, 20 rader etc.). I det här exemplet är det begränsat till 5 rader bara för att hålla kalkylbladet kompakt.

Jag lärde mig denna teknik i Mike Girvins bok Control + Shift + Enter.