Excel-formel: Opslag og transponering af flere kriterier -

Indholdsfortegnelse

Generisk formel

(=INDEX(rng1,MATCH(1,($A1=rng2)*(B$1=rng3),0)))

Resumé

For at udføre en opslag med flere kriterier og transponere resultater til en tabel kan du bruge en matrixformel baseret på INDEX og MATCH. I det viste eksempel er formlen i G5:

(=INDEX(amount,MATCH(1,($F5=location)*(G$4=date),0)))

Bemærk, at denne formel er en matrixformel og skal indtastes med kontrol + skift + enter.

Denne formel bruger også tre navngivne områder: placering = B5: B13, mængde = D5: D13, dato = C5: C13

Forklaring

Kernen i denne formel er INDEX, som henter en værdi fra det navngivne interval "beløb" (B5: B13):

=INDEX(amount,row_num)

hvor række_num er udarbejdet med MATCH-funktionen og noget boolsk logik:

MATCH(1,($F5=location)*(G$4=date),0)

I dette uddrag sammenlignes placeringen i F5 med alle placeringer, og datoen i G4 sammenlignes med alle datoer. Resultatet i hvert tilfælde er en matrix med SAND og FALSK værdi. Når disse arrays multipliceres sammen, tvinger matematikoperationen de SANDE og FALSKE værdier til ens og nuller, så opslagsarrayet, der går ind i MATCH, ser sådan ud:

(1;0;0;0;0;0;0;0;0)

MATCH er indstillet til at matche 1 som et nøjagtigt match og returnerer positionen til INDEX som et række nummer. Nummer 1 fungerer efter opslagsværdien, fordi arrayet nu kun indeholder 1'er og 0'er, som vist ovenfor.

F5 og G4 indtastes som blandede referencer, så formlen kan kopieres gennem tabellen uden ændringer.