Excel – använda VLOOKUP för att jämföra två listor

Steg 1: Det finns några regler så vi kontrollerade dessa först

kunden hade skickat ett mycket grundläggande Excel-kalkylblad som visade en lista över sina betalningsposter efter inköpsorder, fakturanummer, belopp och datum.

administratören (låt oss kalla honom kille) genererade sedan en Excel-rapport ur hans företags kontosystem. Vi fortsatte sedan att använda båda Excel-kalkylblad för att göra vår avstämning.

men innan vi kunde börja använda VLOOKUP-funktionen förklarade jag svårigheterna med att få dina data till ett format som säkerställer att VLOOKUP fungerar.

Excel behövde följande:

• inom kundens arbetsbok och Guy arbetsbok behövde det finnas minst en matchande information för varje post. Till exempel hade båda arbetsböckerna fakturanummer och inköpsordernummer för varje transaktionspost. Vi bestämde oss för att använda fakturanumret i Guy arbetsbok för att hitta en matchning för samma fakturanummer i kundens arbetsbok. Denna bit av information kallas ’Lookup värde’.
• Uppslagsvärdet måste vara unikt, det vill säga det kan inte upprepas i kundens lista flera gånger. Lyckligtvis för Guy fanns det bara en post för varje faktura. I business faktura och PO nummer är nästan alltid unika för en enda transaktion så de är utmärkta att använda som Lookup värden. Andra exempel är produktkoder, kund-eller kontonummer, anställdas nummer eller avdelningskoder. Det är alltid bra att kontrollera att det inte finns några dubbletter innan du utför din sökning.
• Uppslagsvärdet måste finnas i den första kolumnen i listan i kundens arbetsbok. Tyvärr var det inte. det var beläget i 2: a kolumnen (kolumn B) så vi klippte och klistrade in kolumnen framför P/O-nr. kolumn. Detta är en viktig regel när du använder VLOOKUP-funktionen. Om Uppslagsvärdet inte finns i den första kolumnen fungerar inte uppslaget.

när vi hade beslutat om vår Lookup värde och omorganiserade listan i kundens arbetsbok var vi redo att skapa vår VLOOKUP.

steg 2: infoga VLOOKUP-funktionen

’V’ i VLOOKUP står för ”vertikal”. En vertikal sökning används för att leta efter specifika data i den första kolumnen i en datatabell. När den hittar raden som innehåller de data du letar efter den studsar den över till en annan kolumn i samma Datatabell och returnerar information från den.

vi bestämde oss för att infoga VLOOKUP-funktionen i Guy ’ s arbetsbok. På så sätt kan vi använda funktionen för att referera till fakturanumret från hans lista och kontrollera om samma fakturanummer i den första kolumnen i kundens lista. Om den hittade en matchning skulle den sedan returnera in i cellen det belopp som kunden hade mot fakturanumret. Vi skulle då sluta med Guy belopp och kundens belopp i samma kalkylblad. Vi kunde då enkelt jämföra de två och ta reda på vilka transaktioner som inte matchade.

vi skapade en ny kolumnrubrik som heter ’Customer’ s Record ’och placerade den i slutet av Guy’ s befintliga lista. Genom att placera cellväljaren på cell E5 satte vi in VLOOKUP-funktionen.

för att infoga funktionen klickade vi på fliken Formler och sedan från gruppen funktionsbibliotek klickade vi på knappen Infoga funktion.

tips: VLOOKUP-funktionen kan också infogas genom att klicka på knappen Infoga funktion på formelfältet.

från rutan välj en kategori valde vi sökning och referens. I listrutan Välj en funktion valde vi LETARAD och klickade sedan på OK.

vid denna tidpunkt behövde vi ange lämpliga värden i Funktionsargumentrutorna. Jag ska gå igenom var och en av argumenten.

• Lookup_value – detta var fakturanumret i cell C5. När vi skrev in detta i Lookup Value box Excel höll det i minnet och väntade på oss att slutföra de återstående argumenten.

• Table_array-tabellmatrisen är listan som innehåller transaktionsinformationen i kundens kalkylblad. LETARAD kommer att leta efter en matchning för ’Lookup värde’ i den första kolumnen i tabellen array. Guy navigerade till kundens kalkylblad och klickade sedan och släpade över hela området som innehöll alla transaktionsuppgifter. Han inkluderade kolumnen ’Datum’, men A4:C26 skulle också ha varit bra eftersom kolumnen’ Belopp ’ ligger inom detta intervall. Observera att Guy gjorde intervallet absolut så att formeln lätt kunde kopieras.

• Col_index_num-det här är kolumnen från vilken Guy ville dra igenom kundens fakturabelopp. Detta var kolumn 3 i kundens transaktionslista. Kolumner numreras från vänster till höger med den första kolumnen i tabellmatrisområdet som kolumn 1. Var försiktig så att du inte blandar kolumnnummer och kolumnbokstäver. Även om fakturabeloppet hålls i kolumn ’C’ Excel vill bara indexnumret platsen för kolumnen, inte kolumnbokstaven.

• Range_lookup-Range Lookup styr om du söker efter en exakt matchning till din ’Lookup värde’. Jag förklarade för Guy att om han angav sant eller lämnade rutan tom Excel skulle söka efter hans fakturanummer men om det inte kunde hitta en match skulle det returnera det näst största värdet som är mindre än fakturanumret. Om den till exempel inte kunde hitta faktura 2466 skulle den hitta posten för 2465 och returnera beloppet för det istället. För att detta ska fungera behövde kundens lista sorteras efter fakturanummer först. Men om’ FALSE ’ skrivs in i rutan söker Excel efter en exakt matchning för Uppslagsvärdet. Guy ville ha en exakt matchning för sitt fakturanummer så han skrev in falskt i lådan. Jag passerade också på spetsen att du kan ange 0 (noll) i stället för ordet ’FALSE’ och Excel kommer att göra samma sak.

med alla argument in killen klickade OK.

Excel gick sedan igenom var och en av de värden som anges i argumentrutan. Funktionen använde fakturanumret (Uppslagsvärde) för att leta efter ett matchande fakturanummer i kolumnen längst till vänster i kundens poster (Tabellmatris). När fakturanumret hittades hoppade det sedan över det angivna antalet kolumner (kolumnindexnummer) på samma rad för att returnera fakturabeloppet.

tack och lov, som du kan se nedan, hade kunden samma fakturavärde som Guy.

Guy kopierade sedan VLOOKUP-funktionen för alla fakturatransaktioner.
inom några sekunder kunde han se tre # N/A fel (värde inte tillgängliga fel). Detta visade var kunden faktiskt inte har ett register över dessa fakturor. Han fann sedan snabbt skillnaderna i hans och kundens fakturabelopp. Omedelbart kunde han se att fakturan 2464 var kort $100.00, troligen ett nyckelfel.

steg 3: dubbelkontrollen

vi hade nu framgångsrikt jämfört våra poster med kundens. men som en dubbelkontroll bestämde vi oss sedan för att göra en jämförelse från kundens poster, bara om vi hade missat något.

den här gången skapade vi VLOOKUP i kundens kalkylblad. Det första vi behövde göra var att flytta fakturakolumnen så att den var den första kolumnen i vår transaktionslista (Tabellmatris). Efter att ha gjort detta satte vi sedan in VLOOKUP-funktionen i en ny kolumn som heter ’Our record’.

Guy kopierade funktionen ner och skapade sedan kolumnen ’jämförelse’. Vår dubbelkontroll bekräftade att kunden hade under betalat us $100 På Faktura 2464. Vår kontroll visade också att kunden hade fullt möjligt antecknat fakturanumret för faktura 2466 som 2646.

slutsats

Guy kunde nu gå tillbaka till kunden för att diskutera den information som kan ha nycklats av misstag och att leverera kopior av fakturor som kunden inte har ett register över mottagande.

naturligtvis sparade VLOOKUP-funktionen Guy och hans arbetskamrater en enorm tid. Utbildnings-och utvecklingschefen kontaktade mig efter träningen för att säga att den förbättrade effektiviteten i avdelningen hade sparat sitt företag 10s av 1000s Dollar.

vi täcker VLOOKUP-funktionen i våra Excel Stage 3-kurser. Om du har att göra med stora data är denna utbildning definitivt för dig.