Excel-formel: Sökning och transponering av flera kriterier -

Innehållsförteckning

Generisk formel

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

Sammanfattning

För att utföra en flerkriteriekoppling och transponera resultat till en tabell kan du använda en matrisformel baserad på INDEX och MATCH. I exemplet som visas är formeln i G5:

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

Observera att denna formel är en matrisformel och måste anges med kontroll + skift + enter.

Denna formel använder också tre namngivna områden: plats = B5: B13, mängd = D5: D13, datum = C5: C13

Förklaring

Kärnan i denna formel är INDEX, som hämtar ett värde från det angivna intervallet "mängd" (B5: B13):

=INDEX(amount,row_num)

där row_num utarbetas med MATCH-funktionen och lite boolesk logik:

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

I detta utdrag jämförs platsen i F5 med alla platser och datumet i G4 jämförs med alla datum. Resultatet i varje fall är en matris med SANT och FALSKA värden. När dessa matriser multipliceras tillsammans tvingar matteoperationen de Sanna och FALSKA värdena till ens och nollor, så att uppslagsmatrisen som går in i MATCH ser ut så här:

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

MATCH är inställd på att matcha 1 som en exakt matchning och returnerar positionen till INDEX som ett radnummer. Siffran 1 fungerar för uppslagsvärdet eftersom matrisen nu bara innehåller 1 och 0, som visas ovan.

F5 och G4 anges som blandade referenser så att formeln kan kopieras genom tabellen utan modifiering.