Rapporten en exports combineren - X.zoeken

Picqer kent verschillende rapportages en exports. Soms wil je data uit twee exports combineren. Dit kan met de formule XLOOKUP of X.Zoeken, de huidige variant op verticaal zoeken.

Gebruik je Excel 2019 of een oudere versie van Excel? Dan is X.zoeken niet beschikbaar en maak je gebruik van Verticaal zoeken in Excel.

X.zoeken

Met de formule X.zoeken in Excel voeg je data uit de ene tabel toe aan een andere tabel, zo lang er één gegeven overeenkomt.

Zo werkt X.Zoeken in Excel

In deze video laten we je zien wat X.Zoeken is en hoe je het toepast.

De formule uitgelegd

Bekijk tabel 1 met productcodes en namen en tabel 2 met productcodes, leveranciers en prijzen. We willen de prijs van een product uit tabel 2 toevoegen aan tabel 1, zodat we een tabel krijgen met productcode, naam en prijs.

Tabel 1

Tabel 2

De formule voor X.Zoeken ziet er als volgt uit:

=X.ZOEKEN(zoekwaarde; zoeken-matrix; matrix_retourneren; [indien_niet_gevonden];[overeenkomstmodus];[zoekmodus])

In beide tabellen komt de productcode voor. We gebruiken dat gegeven om de tabellen te koppelen. In tabel 1 in cel C2 plaatsen we de formule, zodat hier de prijs komt te staan.

Als zoekwaarde gebruiken we de productcode. We voeren bovenstaande formule in en vervangen zoekwaarde door A2. Daarmee zoeken we op de waarde die in cel A2 staat. Voeg er &”” aan toe om te zorgen dat numerieke waardes goed gevonden worden, waardoor het resultaat wordt:

A2&""

We zoeken in tabel 2, dus we vervangen zoeken-matrix door een verwijzing naar deze kolom. Als de kolom op een apart blad of sheet staat geef je de naam van het blad ook op. Als je op de header van de kolom klikt waarin je wil zoeken komt het automatisch goed in de formule te staan. Het resultaat om kolom A van tabel 2 als zoekwaarde te selecteren is:

Blad2!A:A

Via matrix_retourneren vertellen we Excel uit welke kolom de waarde opgehaald moet worden. In ons geval willen we de prijs van de producten ophalen uit kolom C van tabel 2. Als je op de header van de kolom klikt waaruit je de waardes wil ophalen komt het automatisch goed in de formule te staan. Het resultaat om kolom C van tabel 2 te selecteren is:

Blad2!C:C

Optioneel: Indien_niet_gevonden. Als Excel de zoekwaarde niet kan vinden in de opgegeven zoeken-matrix geef je hier aan welke waarde geretourneerd mag worden. Je kunt hier zowel tekst als numerieke waardes ingeven. Tekst moet altijd tussen dubbele aanhalingstekens staan. Bijvoorbeeld: “Productcode niet gevonden”.

"Productcode niet gevonden"

Optioneel: Overeenkomstmodus geeft aan of het zoeken exact of ongeveer gedaan mag worden. In de meeste gevallen wil je dat de waarde die je zoekt exact overeen komt. Een exacte overeenkomst geef je aan door: ‘0’.

Optioneel: via Zoekmodus geef je aan op welke manier Excel de zoekwaarde zoekt. De meeste gebruikte opties hierbij zijn:

1: Zoek vanaf het eerste item (van boven naar beneden)

-1: Zoek vanaf het laatste item (van beneden naar boven)

In ons geval maakt het niet uit in welke richting we zoeken, omdat iedere zoekwaarde maar 1 keer voorkomt. We kiezen daarom voor de standaard (1).

De totale formule is:

=X.ZOEKEN(A2&"";Blad2!A:A;Blad2!C:C;“Productcode niet gevonden” ;0;1)

Wil je voor de optionele velden: indien_niet_gevonden, overeenkomstmodus en zoekmodus de standaard waarden gebruiken, dan laat je deze velden leeg:

=X.ZOEKEN(A2&"";Blad2!A:A;Blad2!C:C;;;)

Voorbeeld: inkoopbestelregels met HS-codes, gewicht en inkoopprijs

We combineren de productexport met de inkoopbestelregels export.

Achtergrond

Mogelijk doe je periodieke aangiftes bij de douane (CBS of Intrastat). Hierbij worden verschillende gegevens gevraagd voor inkopen en verkopen. Denk daarbij aan de HS-code, het gewicht en de inkoopprijs van gedane inkopen.

Rapporten

In Picqer kun je een export maken van alle gedane inkoopbestellingen in een bepaalde periode. De export van de inkoopbestelregels bevat geen HS-code, gewicht en inkoopprijs. Deze informatie staat wel in je productexport. We combineren daarom de productexport met de inkoop bestelling regels export.

Ga in Picqer naar Inkoop > Export > Exporteer inkoopbestelling regels. Je krijgt nu een Excel-bestand met alle bestelregels per inkoopbestelling

Ga in Picqer naar Producten > Import / Export > Exporteer producten

Kopieer de data uit de producten export naar een nieuw blad in de inkoopbestelling export.

Open het blad met de inkoopbestelling export. In kolom M (eerste lege kolom) willen we nu de HS-code toevoegen die bij het product hoort.

Het overeenkomende gegeven is de productcode. Die staat in kolom F van de bestelling export en in kolom A van de productexport. We willen de gegevens uit kolom U. Als 'indien niet gevonden' waarde geven we op 'Niet gevonden'. We willen dat er exact wordt gezocht, dus gebruiken daarvoor waarde 0.

Voeg de volgende formule in:

=X.ZOEKEN(F2&"";'Producten Export'!A:A;'Producten Export'!U:U;"Niet gevonden";0;)

Hetzelfde doe je voor "Country of Origin" en "Fixed stock price". Je past alleen de kolomwaarde aan naar de kolom die bij deze waardes hoort.