Dubbel verticaal zoeken of index?

[gcoensen]

Hoi allemaal,

Ik ben sinds dit jaar bezig met een controle op facturen. Ik heb inmiddels een model gebouwd waarin een aantal dingen semi-automatisch gecheckt worden. Ik loop hier echter tegen een probleem aan: (zie bijgevoegde excel sheet)

Bekijk bijlage Factuur controle periode 3.xlsx

Als ik een itemcode invoer in kolom B, wil ik in kolom D het bedrag in euro's dat bij die itemcode hoort. Echter bij elke itemcode maken we gebruik van verschillende valuta. Daardoor staat elke itemcode er vaker in. Bij verticaal zoeken geeft hij de waarde van de eerste keer dat Excel de itemcode tegen komt en dat is nooit EUR. Ik heb op verschillende fora gezocht maar uit alle voorbeelden kan ik niet de juiste oplossing vinden. Ik heb een simpel model nagebouwd (in werkelijkheid is het uitgebreider en groter, maar de essentie van het probleem is duidelijk op deze manier). Alle waarden zijn fictief vanwege de vertrouwelijkheid van gegevens.

Hopelijk kan iemand bij hierbij helpen!

Alvast bedankt!

 

[popipipo]

=SOMPRODUCT((Data!$A$1:$A$34=B2)*(Data!$D$1:$D$34="eur")*(Data!$C$1:$C$34))

Probeer deze formule eens.

Zorg er wel voor dat de bedragen ook echt bedragen (getallen) zijn en niet zoals nu hier en daar tekst.

 

[gcoensen]

Geweldig de formule werkt!! Ik snap alleen niet hoe het kan dat hij zo werkt. Het lijkt een rekenkundige formule door de vermenigvuldigingen.

Hoe dan ook, ik kan weer verder met mijn model Hartstikke bedankt!

 

[gcoensen]

Hmm ik loop tegen een nieuw probleem aan. In werkelijkheid is die lijst met data meer dan 31.000 regels groot, een flinke lijst dus. Nu is het zo dat ik die lijst steeds opnieuw aangeleverd krijg wanneer er iets verandert in de lijst. Dat is zo'n 4 keer per maand. Het probleem van die lijst is dat deze vanuit SAP wordt geëxporteerd, waardoor alle kommagetallen met punten worden weergegeven. Dit heb ik opgelost door een macro die automatisch die punten in komma's verandert. Alleen het probleem is dat een getal als: 8,345.00 verandert in 8,345,00. Zodra er ook maar één dergelijke fout in de data zit, is de uitkomst van de formule meteen #N/A of VALUE. Weet je een handige oplossing die ervoor zorgt dat 8,345.00 verandert in 8345,00? Rekening houdend met de macro die hem eerst verandert in 8,345,00. Ik kan uiteraard de macro aanpassen en een extra handeling erin zetten die plaatsvindt vóór die conversie van punten in komma's, maar welke? Een voorbeeld bestand kan ik nu niet toevoegen, wederom vanwege vertrouwelijkheid.

Het gaat zoals gezegd om 31000+ regels, dus ik verwerk het het liefst in een macro zodat je niet telkens alle data hoeft te selecteren. Daarnaast moet de controletool ook door andere personen gebruikt kunnen worden zonder uitleg, dus een macro is haast een vereiste.

Nogmaals dank!

 

[popipipo]

Verwijder eerst alle komma's (zoek op ',' en vervang door niets)
Je bent dan de komma's kwijt, daarna de 'punten' vervangen door 'komma's'

Als je de verschillende bereiken dynamisch maakt d.m.v. namen gaat het ook goed bij een groter data bestand.

 

[gcoensen]

Maar natuurlijk :thumb: De oplossing is vaak simpeler dan je denkt. Ook het gebruik van namen is een goede toevoeging, many thanks!