Generieke formule
(=INDEX(rng1,MATCH(1,($A1=rng2)*(B$1=rng3),0)))
Samenvatting
Als u op meerdere criteria wilt zoeken en resultaten in een tabel wilt transponeren, kunt u een matrixformule gebruiken op basis van INDEX en MATCH. In het getoonde voorbeeld is de formule in G5:
(=INDEX(amount,MATCH(1,($F5=location)*(G$4=date),0)))
Merk op dat deze formule een matrixformule is en moet worden ingevoerd met control + shift + enter.
Deze formule gebruikt ook drie benoemde bereiken: locatie = B5: B13, bedrag = D5: D13, datum = C5: C13
Uitleg
De kern van deze formule is INDEX, waarmee een waarde wordt opgehaald uit het benoemde bereik "amount" (B5: B13):
=INDEX(amount,row_num)
waarbij row_num wordt uitgewerkt met de MATCH-functie en wat booleaanse logica:
MATCH(1,($F5=location)*(G$4=date),0)
In dit fragment wordt de locatie in F5 vergeleken met alle locaties en wordt de datum in G4 vergeleken met alle datums. Het resultaat is in elk geval een reeks TRUE en FALSE waarden. Wanneer deze arrays met elkaar worden vermenigvuldigd, voegt de wiskundige bewerking de waarden TRUE en FALSE toe aan enen en nullen, zodat de lookup-array die naar MATCH gaat er als volgt uitziet:
(1;0;0;0;0;0;0;0;0)
VERGELIJKEN is ingesteld om overeen te komen met 1 als een exacte overeenkomst, en retourneert de positie naar INDEX als een rijnummer. Het getal 1 werkt voor de opzoekwaarde omdat de array nu alleen enen en nullen bevat, zoals hierboven weergegeven.
F5 en G4 worden ingevoerd als gemengde verwijzingen, zodat de formule zonder wijziging door de tabel kan worden gekopieerd.
Transponeren met speciale pasta
Als u een tabel slechts één keer hoeft te transponeren, vergeet dan niet dat u speciaal plakken kunt gebruiken.
