Opslag med flere kriterier med INDEX-MATCH

Det grundlæggende trick er at gange de to betingelsestest med hinanden inde i MATCH.

(A2:A10=G1) giver et array af TRUE/FALSE for det første kriterium. (B2:B10=G2) gør det samme for det andet. Når du ganger dem, giver (A2:A10=G1)*(B2:B10=G2) 1 kun dér, hvor begge er TRUE, og 0 alle andre steder.

MATCH leder derefter efter værdien 1 for at finde den række, der opfylder begge betingelser.

I regneark fungerer TRUE som 1 og FALSE som 0. Multiplikation af to af disse efterligner en logisk AND:

• 1 gange 1 = 1 (begge betingelser er opfyldt)
• 1 gange 0 = 0
• 0 gange 1 = 0
• 0 gange 0 = 0

Det er altså kun rækker, hvor begge kriterier er opfyldt, der giver 1. Alle andre rækker bliver til 0. Det ene 1-tal markerer den række, vi ønsker.

Pak nu det multiplicerede array ind i MATCH, og søg efter den eksakte værdi 1.

MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0) returnerer positionen for den første række, hvor begge betingelser er TRUE.

Hvis den matchende kombination findes i den fjerde datarække, returnerer MATCH 4. Det er den position, INDEX skal bruge for at hente svaret.

Giv MATCH-resultatet videre til INDEX over den kolonne, du faktisk vil bruge, for eksempel prisen i C2:C10.

Den komplette formel betyder: Fra C2:C10 returneres værdien på den række, hvor produktet er lig med G1 og størrelsen er lig med G2.

Dette er et ægte opslag med flere kriterier, uden hjælpekolonne og uden at omarrangere dine data.

Denne formel evaluerer arrays af betingelser. I moderne Excel og Google Sheets skal du blot trykke på Enter, så fungerer den.

I ældre Excel (før dynamiske arrays) skal du bekræfte den som en matrixformel med Ctrl+Shift+Enter, hvilket tilføjer krøllede parenteser. Hvis resultatet er forkert eller viser en fejl i ældre Excel, er dette bekræftelsestrin som regel det, der mangler.

Har du brug for tre kriterier? Så gang blot endnu en test med. Antag, at du også vil matche en farve i kolonne D med inputtet G3.

Hver ekstra (range=criterion)-faktor indsnævrer resultatet yderligere. Kun rækker, hvor alle betingelser er TRUE, bevarer et produkt på 1; enhver FALSE-værdi gør hele produktet til 0.

Mønstret kan udvides til så mange kolonner, som du har brug for.

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

• G1 = "Shirt", G2 = "Large".
• Betingelses-arrayene giver kun 1 på rækken med Shirt+Large, for eksempel række 4.
• MATCH(1, ..., 0) returnerer 4.
• INDEX(C2:C10, 4) returnerer prisen fra den række.

Hvis du ændrer et af inputtene, finder formlen straks den rigtige række igen.

Husk dette:

• Områder af samme størrelse: alle betingelsesområder og INDEX-kolonnen skal have samme højde.
• Intet match: Hvis ingen række opfylder alle kriterier, returnerer MATCH #N/A. Pak hele udtrykket ind i IFERROR.
• Dubletter: Hvis mere end én række matcher, returnerer MATCH kun den første. Gør dine kriterier tilstrækkeligt specifikke til at identificere én række entydigt.

Hvis flere rækker kan matche, og du hellere vil summere deres værdier end hente én enkelt, er SUMPRODUCT et enkelt alternativ til INDEX-MATCH, der indtastes som matrixformel.

Funktionen ganger betingelses-arrayene med værdikolonnen og lægger resultaterne sammen, så kun rækker, der opfylder begge kriterier, bidrager. Du behøver ikke Ctrl+Shift+Enter, fordi SUMPRODUCT håndterer arrays direkte.

Brug INDEX-MATCH til at hente én matchende værdi, og brug SUMPRODUCT til at aggregere alle match.

Ved opslag efter flere kriterier med INDEX-MATCH:

• Gang betingelsesarrayerne med hinanden: (A=G1)*(B=G2) giver kun 1, hvor alle betingelser er opfyldt (et logisk OG).
• MATCH(1, ..., 0) finder rækkens position.
• INDEX(returnCol, position) returnerer værdien.

Tilføj flere *(range=criterion)-faktorer for ekstra betingelser, sørg for at områderne har samme højde, bekræft med Ctrl+Shift+Enter i ældre Excel-versioner, og beskyt med IFERROR.