INDEX/MATCH - geen exacte match

[jansm]

Hallo dames/heren,
ik heb een probleem met de index-functie.
Sheet TRANS bevat een aantal transactie's met bijbehorende gegevens. In sheet OVERZICHT probeer ik het saldo te halen per datum en rekeningnummer. Is er van een gevraagd rekeningnummer op een gevraagde datum geen transactie geweest dat krijg ik de melding #N/A. Ik zou dan graag het saldo willen hebben van de laatste transactie van dat rekeningnummer. Dus van 16/9-2015 en rekeningnummer 999888777 het saldo van 14-9-2015 (€ 646.27). Is dat te regelen?

Bekijk bijlage test.xlsx

Mvg, jansm

 

[MarcelBeug]

Ja. Stel dat je data loopt van regel 2 tot maximaal 1000, in OVERZICHT!E5 en kopiëren naar beneden, de volgende matrixformule (na plakken bevestigen met Ctrl+Shift+Enter, niet alleen Enter):

=INDEX(TRANS!$H$2:$H$1000;MATCH(B5;IF(TRANS!$A$2:$A$1000<=A5;TRANS!$D$2:$D$1000);0))

Ik heb de Nederlandse functiebenamingen maar even vertaald naar het Engels.

Een matrixformule is herkenbaar aan de accoladen {} die Excel er zelf omheen zet als je op Ctrl+Shift+Enter drukt.

 

[jansm]

dit werkt niet.
Bij een match krijg ik #N/A en bij geen match een verkeerd saldo.
Ik koppel in mijn voorbeeld datum en rekeningnummer (..&..) en gebruik dit als zoekcriterium. Is het niet mogelijk (beter) om te extraheren middels 2 criteria, datum en rekeningnr?

 

[ExcelAmateur]

jansm,

Bij mij werkt het wel, zie bestandje.

 

[MarcelBeug]

Dank ExcelAmateur. Ik zal wat konsekwenter zijn in het bijvoegen van bestandjes.

@JansM
Nog een opmerking over de oplossing: dit werkt alleen zolang de boekdatum aflopend gesorteerd is, zoals nu het geval is.
In feite wordt geëxtraheerd op basis van 2 criteria, maar daarom hoeven zo nog niet aan elkaar gekoppeld te worden.

 

[ExcelAmateur]

JansM,

Werkt het nu?

 

[jansm]

Hallo Marcel en 'Amateurtje'
zojuist ff meer tijd gehad en oplossing getest en ..... het werkt. De eerste keer was ik te gehaast met testen. Zal vanavond er beter naar kijken en proberen te begrijpen hoe, jullie profs, dit opgelost hebben.
Beiden bedank1!!

 

[ExcelAmateur]

De eer is helemaal voor Marcel, die heb de formule gemaakt.
Ik ben niet thuis in MatrixFormules.

Vergeet niet om de vraag als opgelost te zetten, dank u.