Oppslag med flere kriterier med INDEX-MATCH

Kjernen i trikset er å multiplisere de to betingelsestestene inne i MATCH.

(A2:A10=G1) gir en matrise med TRUE/FALSE for det første kriteriet. (B2:B10=G2) gjør det samme for det andre. Når De multipliserer dem, gir (A2:A10=G1)*(B2:B10=G2) 1 bare der begge er TRUE, og 0 ellers.

MATCH søker deretter etter verdien 1 for å finne raden som oppfyller begge betingelsene.

I regneark oppfører TRUE seg som 1 og FALSE som 0. Ved å multiplisere to slike verdier etterligner De en logisk AND:

• 1 ganger 1 = 1 (begge betingelser er oppfylt)
• 1 ganger 0 = 0
• 0 ganger 1 = 0
• 0 ganger 0 = 0

Det er derfor bare rader der begge kriteriene er oppfylt, som gir 1. Alle andre rader blir 0. Denne ene 1-eren markerer raden vi ønsker.

Pakk nå den multipliserte matrisen inn i MATCH, og søk etter den eksakte verdien 1.

MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0) returnerer posisjonen til den første raden der begge betingelsene er TRUE.

Hvis kombinasjonen som samsvarer, ligger i den fjerde dataraden, returnerer MATCH 4. Dette er posisjonen INDEX trenger for å hente svaret.

Bruk MATCH-resultatet som inndata til INDEX over kolonnen De faktisk vil hente fra, for eksempel prisen i C2:C10.

Den komplette formelen betyr: Fra C2:C10 skal verdien på raden der produktet er lik G1 og størrelsen er lik G2, returneres.

Dette er et ekte oppslag med flere kriterier, uten hjelpekolonne og uten omorganisering av dataene.

Denne formelen evaluerer matriser med betingelser. I moderne Excel og Google Sheets trykker De ganske enkelt på Enter, så fungerer den.

I eldre Excel (før dynamiske matriser) må De bekrefte den som en matriseformel med Ctrl+Shift+Enter, noe som legger til krøllparenteser. Hvis resultatet er feil eller viser en feilmelding i eldre Excel, er dette bekreftelsestrinnet vanligvis det som mangler.

Trenger De tre kriterier? Multipliser ganske enkelt inn én test til. Anta at De også vil samsvare med en farge i kolonne D mot inndataene i G3.

Hver ekstra (range=criterion)-faktor begrenser resultatet ytterligere. Bare rader der alle betingelsene er TRUE, beholder et produkt på 1. Én FALSE-verdi gjør hele produktet til 0.

Mønsteret kan utvides til så mange kolonner som De trenger.

Data: A = produkt, B = størrelse, C = pris. De vil ha prisen på en "Shirt" i størrelsen "Large".

• G1 = "Shirt", G2 = "Large".
• Betingelsesmatrisene produserer 1 bare på raden for Shirt+Large, for eksempel rad 4.
• MATCH(1, ..., 0) returnerer 4.
• INDEX(C2:C10, 4) returnerer prisen på denne raden.

Endre en av inndataene, så finner formelen riktig rad på nytt umiddelbart.

Husk dette:

• Områder med samme størrelse: alle betingelsesområdene og INDEX-kolonnen må ha samme høyde.
• Ingen treff: hvis ingen rad oppfyller alle kriteriene, returnerer MATCH #N/A. Pakk hele uttrykket inn i IFERROR.
• Duplikater: hvis flere enn én rad samsvarer, returnerer MATCH bare den første. Gjør kriteriene spesifikke nok til at treffet blir unikt.

Hvis flere rader kan samsvare, og De heller vil summere verdiene enn å hente én av dem, er SUMPRODUCT et ryddig alternativ til matriseangitt INDEX-MATCH.

Den multipliserer betingelsesmatrisene med verdikolonnen og legger sammen resultatene, slik at bare rader som oppfyller begge kriteriene, bidrar. Det er ikke nødvendig med Ctrl+Shift+Enter, fordi SUMPRODUCT håndterer matriser innebygd.

Bruk INDEX-MATCH til å hente én enkelt samsvarende verdi, og SUMPRODUCT til å aggregere alle samsvarene.

For oppslag med flere kriterier og INDEX-MATCH:

• Multipliser betingelsesmatrisene med hverandre: (A=G1)*(B=G2) gir 1 bare der alle betingelsene er oppfylt (et logisk AND).
• MATCH(1, ..., 0) finner posisjonen til raden.
• INDEX(returnCol, position) returnerer verdien.

Legg til flere *(range=criterion)-faktorer for ekstra betingelser, sørg for at områdene har samme høyde, bekreft med Ctrl+Shift+Enter i eldre Excel-versjoner, og bruk IFERROR som sikkerhet.