Generisk formel
=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")
Sammendrag
En måte å trekke ut flere treff i Excel er å bruke INDEX og MATCH med en hjelpekolonne som flagger samsvarende data. Dette unngår kompleksiteten til en mer avansert matriseformel. I eksemplet vist er formelen i H6:
=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")
der ct (G3), data (B3: E52) og hjelper (E3: E52) er navngitte områder.
Forklaring
Utfordringen med oppslagsformler som henter mer enn en kamp, er å administrere duplikater (dvs. flere treff). Oppslagsformler som VLOOKUP og INDEX + MATCH kan lett finne den første kampen, men det er mye vanskeligere å slå opp "alle treff" når kriteriene finner mer enn en kamp.
Denne formelen håndterer denne utfordringen ved å bruke en hjelpekolonne som returnerer en numerisk verdi som kan brukes til å enkelt trekke ut flere treff. Formelen i hjelpekolonnen ser slik ut:
=SUM(E2,AND(C3=$I$3,D3=$J$3))
Hjelpekolonnen tester hver rad i dataene for å se om avdelingen i kolonne C samsvarer med verdien i I3 og bygningen i kolonne D samsvarer med verdien i J3. Begge logiske testene må returnere SANT for at AND skal returnere SANT.
For hver rad blir resultatet fra AND-funksjonen lagt til "verdien over" i hjelpekolonnen for å generere et antall. Den praktiske effekten av denne formelen er en økende teller som bare endres når en (ny) kamp blir funnet. Da forblir verdien den samme til neste kamp er funnet. Dette fungerer fordi SANN / FALSK-resultatene returneres av OG blir tvunget til 1/0-verdier som en del av sumoperasjonen. FALSE resultater legger ikke til noe, og SANTE resultater legger til 1.
Tilbake i ekstraksjonsområdet ser oppslagsformelen for Navn i kolonne H slik ut:
=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")
Arbeidet fra innsiden og ut, INDEX + MATCH-delen av formelen, slår opp navnet på den første kampen som ble funnet, ved å bruke radnummeret i kolonne G som samsvarverdi:
INDEX(data,MATCH($G6,helper,0),1)
INDEX mottar alle de tre kolonnene med data som matrisen (kalt område "data"), og MATCH er konfigurert til å matche radnummeret i hjelpekolonnen (det navngitte området "hjelper") i nøyaktig samsvarsmodus (tredje argument satt til null) .
Dette er hvor smart formelen blir tydelig. Hjelperkolonnen inneholder åpenbart duplikater, men det spiller ingen rolle, fordi MATCH vil bare matche den første verdien. Etter design tilsvarer hver "første verdi" riktig rad i datatabellen.
Formlene i kolonnene I og J er de samme som H, bortsett fra kolonnetallet, som i hvert tilfelle økes med en.
IF-setningen som omslutter INDEX / MATCH-formelen, utfører en enkel funksjon - den sjekker hvert radnummer i ekstraksjonsområdet for å se om radnummeret er mindre enn eller lik verdien i G3 (kalt område "ct"), som er det totale antallet antall samsvarende poster. I så fall kjøres INDEX / MATCH-logikken. Hvis ikke, utfører IF en tom streng ("").
Formelen i G3 (kalt område "ct") er enkel:
=MAX(helper)
Siden maksimumsverdien i hjelpekolonnen er den samme som det totale antall kamper, er MAX-funksjonen alt vi trenger.
Merk: ekstraksjonsområdet må konfigureres manuelt for å håndtere så mye data som nødvendig (dvs. 5 rader, 10 rader, 20 rader osv.). I dette eksemplet er det begrenset til fem rader bare for å holde regnearket kompakt.
Jeg lærte denne teknikken i Mike Girvins bok Control + Shift + Enter.
FILTER-funksjonen
Hvis du har Dynamic Array-versjonen av Excel, er FILTER-funksjonen langt enklere å hente ut alle samsvarende data.
