Excel – gebruik VLOOKUP om twee lijsten

te vergelijken Stap 1: Er zijn een paar regels, dus we hebben deze eerst gecontroleerd

de klant had een zeer eenvoudig Excel-werkblad gestuurd met een lijst van hun betalingsrecords op bestelling, factuurnummer, bedrag en datum.

de beheerder (laten we hem Guy noemen) genereerde vervolgens een Excel-rapport uit het boekhoudsysteem van zijn bedrijf. We gingen vervolgens beide Excel-werkbladen gebruiken om onze verzoening te doen.

echter, voordat we de functie VLOOKUP konden gebruiken, legde ik de fijne kneepjes uit van het krijgen van uw gegevens in een formaat dat ervoor zorgt dat de VLOOKUP werkt.

Excel had het volgende nodig:

• binnen de werkmap van de klant en Guy ‘ s werkmap moest er ten minste één bijpassende stukje informatie voor elk record. Beide werkmappen hadden bijvoorbeeld het factuurnummer en het inkoopordernummer voor elke transactierecord. We hebben besloten om het factuurnummer in Guy ‘ s werkmap te gebruiken om een match te vinden voor hetzelfde factuurnummer in de werkmap van de klant. Dit stukje informatie wordt de ‘Lookup Value’genoemd.
• de opzoekwaarde moet uniek zijn, dat wil zeggen dat deze niet meerdere keren in de lijst van de klant kan worden herhaald. Gelukkig voor Guy was er maar één record voor elke factuur. In zakelijke factuur en PO nummers zijn bijna altijd uniek voor een enkele transactie, zodat ze uitstekend te gebruiken als Lookup waarden. Andere voorbeelden zijn productcodes, klant-of rekeningnummers, medewerkersnummers of afdelingscodes. Het is altijd een goede gewoonte om te controleren of er geen duplicaten zijn voordat u uw VLOOKUP uitvoert.
• de opzoekwaarde moest worden gevonden in de eerste kolom van de lijst in de werkmap van de klant. Helaas was het niet. het was gelegen in de 2e kolom (kolom B) dus hebben we gesneden en geplakt de kolom voor de P/O No. kolom. Dit is een belangrijke regel bij het gebruik van de functie VERT.zoeken. Als de opzoekwaarde niet in de eerste kolom staat, zal de opzoekwaarde niet werken.

zodra we onze Lookup-waarde hadden besloten en de lijst in de werkmap van de Klant hadden gereorganiseerd, waren we klaar om onze VLOOKUP te maken.

Stap 2: het invoegen van de VLOOKUP-functie

de ‘ V ‘in VLOOKUP staat voor “verticaal”. Een verticale lookup wordt gebruikt om specifieke gegevens te zoeken in de eerste kolom van een gegevenstabel. Zodra het de rij vindt die de gegevens bevat die u zoekt, stuitert het naar een andere kolom in dezelfde tabel met gegevens en retourneert het Informatie ervan.

we hebben besloten om de functie VERT.zoeken in Guy ‘ s werkmap in te voegen. Op die manier konden we de functie gebruiken om het factuurnummer van zijn lijst te verwijzen en te controleren op hetzelfde factuurnummer in de eerste kolom van de lijst van de klant. Als het een match zou dan terug in de cel het bedrag dat de klant had tegen het factuurnummer. We zouden dan eindigen met Guy ‘ s bedrag en het bedrag van de klant in hetzelfde werkblad. We konden dan gemakkelijk vergelijken de twee en erachter te komen welke transacties niet overeenkomen.

we hebben een nieuwe kolomkop gemaakt met de naam ‘customer’ s Record’, die we aan het einde van Guy ‘ s bestaande lijst plaatsen. Door de celkiezer op cel E5 te plaatsen, hebben we de functie VERT.zoeken ingevoegd.

om de functie in te voegen hebben we op het tabblad Formules geklikt en vervolgens vanuit de Functiebibliotheekgroep op de knop Functie invoegen geklikt.

Tip: de VLOOKUP-functie kan ook worden ingevoegd door op de Functie invoegen te klikken knop op de formulebalk.

in het vak Selecteer een categorie hebben we zoeken en referentie geselecteerd. Van de Selecteer een functie keuzelijst hebben we VLOOKUP geselecteerd en vervolgens op OK geklikt.

op dit punt moesten we de juiste waarden invoeren in de functieargumentenvakken. Ik zal je door elk van de argumenten loodsen.

• Lookup_value – dit was het factuurnummer in cel C5. Zodra we dit in het vak opzoekwaarde hebben ingevoerd, hield Excel het in het geheugen en wachtte tot we de resterende argumenten hadden voltooid.

• Table_array-de tabelarray is de lijst met de transactiegegevens op het werkblad van de klant. VLOOKUP zal zoeken naar een overeenkomst voor de’ Lookup waarde ‘ in de eerste kolom van de tabel array. Guy navigeerde naar het werkblad van de klant en vervolgens klikte en sleepte over het hele gebied dat alle transactiegegevens hield. Hij voegde de kolom ‘Datum’ toe, maar A4:C26 zou ook prima zijn geweest omdat de kolom ‘Bedrag’ binnen dit bereik valt. Houd er rekening mee dat Guy het bereik absoluut heeft gemaakt, zodat de formule gemakkelijk kan worden gekopieerd.

• Col_index_num – Dit is de kolom waaruit Guy het factuurbedrag van de klant wilde halen. Dit was kolom 3 in de transactielijst van de klant. Kolommen zijn van links naar rechts genummerd, waarbij de eerste kolom in het tabelarray-bereik kolom 1 is. Wees voorzichtig om kolomnummers en kolomletters niet door elkaar te halen. Hoewel het factuurbedrag in kolom ‘C’ staat, wil Excel alleen de locatie van het indexnummer van de kolom, niet de kolomletter.

• Range_lookup – de Range Lookup bepaalt of u op zoek bent naar een exacte overeenkomst met uw ‘Lookup waarde’. Ik legde Guy uit dat als hij True ingevoerd of links het vak leeg Excel zou zoeken naar zijn factuurnummer, maar als het niet kon vinden van een match zou het de volgende grootste waarde die kleiner is dan het factuurnummer retourneren. Als het bijvoorbeeld factuur 2466 niet kon vinden, zou het het record voor 2465 vinden en het bedrag daarvoor retourneren. Om dit te laten werken moest de lijst van de klant eerst gesorteerd worden op factuurnummer. Echter, als’ FALSE ‘ wordt ingevoerd in het vak Excel zal zoeken naar een exacte overeenkomst voor de opzoekwaarde. De man wilde een exacte overeenkomst met zijn factuurnummer, dus hij heeft vals ingevoerd in de doos. Ik heb ook de tip doorgegeven dat je 0 (nul) kunt invoeren in plaats van het woord ‘FALSE’ en Excel zal hetzelfde doen.

met alle ingevoerde argumenten Guy klikte OK.

Excel stapte vervolgens door elk van de waarden in het vak argument. De functie gebruikte het factuurnummer (opzoekwaarde) om een passend factuurnummer te zoeken in de linkerkolom van de records van de klant (Tabelreeks). Zodra het factuurnummer werd gevonden, sprong het vervolgens over het opgegeven aantal kolommen (kolomindexnummer) op dezelfde rij om het factuurbedrag te retourneren.

gelukkig had de klant, zoals u hieronder kunt zien, dezelfde factuurwaarde als Guy.

Guy kopieerde vervolgens de VLOOKUP-functie voor alle factuurtransacties.
binnen enkele seconden kon hij drie #N/A fouten zien (waarde geen beschikbare fouten). Dit liet zien waar de klant eigenlijk geen registratie van deze facturen had. Hij ontdekte dan snel de verschillen in zijn en de factuurbedragen van de klant. Onmiddellijk kon hij zien dat factuur 2464 was kort $ 100,00, zeer waarschijnlijk een keying fout.

Stap 3: de dubbele controle

we hadden nu met succes vergeleken onze records met de klant. echter, als een dubbele controle hebben we vervolgens besloten om een vergelijking uit de records van de klant te doen, voor het geval we iets hadden gemist.

deze keer hebben we de VLOOKUP gemaakt in het werkblad van de klant. Het eerste wat we moesten doen was de factuur kolom te verplaatsen, zodat het de eerste kolom in onze transactie lijst (Tabel Array). Na dit te hebben gedaan hebben we vervolgens de VLOOKUP-functie ingevoegd in een nieuwe kolom met de naam ‘ons record’.

Guy kopieerde de functie naar beneden en creëerde vervolgens de kolom ‘vergelijking’. Onze dubbele controle bevestigde dat de klant onder betaalde US $ 100 op factuur 2464. Uit onze controle bleek ook dat de klant het factuurnummer voor factuur 2466 als 2646 had geregistreerd.

conclusie

Guy zou nu terug kunnen gaan naar de klant om de informatie te bespreken die foutief is ingetoetst en om kopieën te verstrekken van de facturen die de klant niet had ontvangen.

onnodig te zeggen dat de VLOOKUP-functie Guy en zijn collega ‘ s enorm veel tijd heeft bespaard. De Training and Development manager nam contact met me op na de training om te zeggen dat de verbeterde efficiëntie in de afdeling hun bedrijf 10s van 1000s dollars had gered.

we behandelen de VLOOKUP-functie in onze Excel Stage 3-cursussen. Als je te maken hebt met grote data is deze training zeker iets voor jou.