Excel VLOOKUP är kraftfullt, men det fungerar inte när du har specifika situationer. Idag, titta på hur du felsöker VLOOKUP.
VLOOKUP är min favoritfunktion i Excel. Om du kan göra VLOOKUP kan du lösa många problem i Excel. Men det finns saker som kan utlösa en VLOOKUP. Detta ämne talar om några av dem.
Men först, grunderna i VLOOKUP på vanlig engelska.
Uppgifterna i A: C kom från IT-avdelningen. Du bad om försäljning efter artikel och datum. De gav dig artikelnummer. Du behöver artikelbeskrivning. I stället för att vänta på att IT-avdelningen ska köra data igen hittar du tabellen som visas i kolumn F: G.
Du vill att VLOOKUP ska hitta objektet i A2 medan det söker igenom den första kolumnen i tabellen i $ F $ 3: $ G $ 30. När VLOOKUP hittar matchningen i F7 vill du att VLOOKUP ska returnera beskrivningen i den andra kolumnen i tabellen. Varje VLOOKUP som letar efter en exakt matchning måste sluta på False (eller noll, vilket motsvarar False). Formeln nedan är korrekt inställd.
Observera att du använder F4 för att lägga till fyra dollartecken till adressen för uppslagstabellen. När du kopierar formeln nedåt i kolumn D behöver du adressen för att uppslagstabellen ska vara konstant. Det finns två vanliga alternativ: Du kan ange hela kolumnerna F: G som uppslagstabell. Eller så kan du namnge F3: G30 med ett namn som ItemTable. Om du använder =VLOOKUP(A2,ItemTable,2,False)fungerar det angivna intervallet som en absolut referens.
Varje gång du gör en massa VLOOKUP, måste du sortera kolumnen med VLOOKUP. Sortera ZA och eventuella # N / A-fel kommer till toppen. I det här fallet finns det en. Objekt BG33-9 saknas i uppslagstabellen. Kanske är det ett stavfel. Kanske är det ett helt nytt föremål. Om det är nytt, infoga en ny rad var som helst i mitten av din uppslagstabell och lägg till det nya objektet.
Det är ganska normalt att ha några # N / A-fel. Men i figuren nedan returnerar exakt samma formel inget annat än # N / A. När detta händer ser jag om jag kan lösa den första VLOOKUP. Du letar upp BG33-8 som finns i A2. Börja kryssa genom den första kolumnen i uppslagstabellen. Som du kan se är matchningsvärdet tydligt i F10. Varför kan du se detta, men Excel kan inte se det?
Gå till varje cell och tryck på F2-tangenten. Här är F10. Observera att insättningsmarkören visas direkt efter 8.
Här är cell A2 i redigeringsläge. Insättningsmarkören är ett par mellanslag bort från 8. Detta är ett tecken på att någon gång lagrades dessa data i en gammal COBOL-datauppsättning. Tillbaka i COBOL, om fältet Artikel definierades som 10 tecken och du bara skrev 6 tecken, skulle COBOL lägga till det med fyra extra mellanslag.
Lösningen? I stället för att leta upp A2, leta upp TRIM(A2).
TRIM () -funktionen tar bort ledande och efterföljande utrymmen. Om du har flera mellanslag mellan ord kommer TRIM att konvertera dem till ett enda mellanslag. I figuren nedan finns mellanslag före och efter båda namnen i A1. =TRIM(A1)tar bort alla utom ett utrymme i A3.
Förresten, tänk om problemet hade varit att följa mellanslag i kolumn F istället för kolumn A? Lägg till en kolumn med TRIM () -funktioner till E, peka på kolumn F. Kopiera dem och klistra in som värden i F så att uppslagningar börjar fungera igen.
Den andra mycket vanliga anledningen till att VLOOKUP inte fungerar visas här. Kolumn F har verkliga siffror. Kolumn A har text som ser ut som siffror.
Välj hela kolumn A. Tryck på alt = "" + D, E, F. Detta gör en standardtext till kolumner och konverterar alla textnummer till riktiga siffror. Sökningen börjar fungera igen.
Kolla på video
- VLOOKUP löser många problem
- Vanliga VLOOKUP-problem:
- Om VLOOKUP börjar fungera men # N / A blir mer framträdande: glömde $ i uppslagstabellen
- Några # N / A: objekt som saknas i tabellen
- Inget av VLOOKUP fungerar: kolla efter bakre utrymmen
- Ta bort bakre utrymmen med TRIM
- Siffror och siffror lagrade som text
- Välj båda kolumnerna och använd alt = "" + DEF
- Avsnittet innehåller ett skämt som både revisorer och IT-personer tycker är roliga, men av olika skäl
Videoutskrift
Lär dig Excel från podcast, avsnitt 2027 - Felsökning VLOOKUP!
Okej, podcasting av hela denna bok, klicka på "i" i det övre högra hörnet för att komma till spellistan!
VLOOKUP är min favoritfunktion i hela Excel, och jag berättar alltid detta skämt i ett av mina seminarier, och det blir skratt oavsett om du är IT-person eller inte IT-person. Jag säger alltid ”Tja, vi har den här datauppsättningen här till vänster, och jag kan titta på den informationen och berätta att data kom från IT-avdelningen för det är precis vad jag bad om, men egentligen inte vad jag behövde. Jag bad om artikeldatum och kvantitet, och de gav mig artikelnummer och kvantitet, men de brydde sig inte om att ge mig beskrivning, eller hur? " Och revisorerna skrattar alltid åt det här, för det här händer dem hela tiden, och IT-killarna är som "Tja, jag gav dig vad du bad om, det är inte mitt fel!" Så de tycker båda att det är roligt av olika skäl, så du vet, och IT-killen är upptagen, han kan inte komma tillbaka till att skriva om frågan,så vi måste göra något för att rädda detta själva.
Och så den här lilla tabellen kopierade jag från någon annanstans på min dator, på vanlig engelska, vad VLOOKUP gör är att säga "Hej, vi har artikelnummer W25-6." Vi har en tabell här, jag vill kryssa ner genom den första kolumnen i tabellen tills jag hittar artikelnumret och sedan returnera något från den raden. Okej, i det här fallet är vad jag vill ha den andra kolumnen från den raden. Och sedan i slutet av varje VLOOKUP måste vi sätta antingen FALSE eller 0. Nu, här, efter att jag valt intervallet, ska jag trycka på F4-tangenten, och sedan vill jag ha den andra kolumnen och sedan FALSE för en exakt match. Välj aldrig ungefärlig match, aldrig, aldrig! Det är inte en ungefärlig match, det är en mycket speciell sak, det fungerar inte hela tiden. Om du vill ändra dina siffror till Securities and Exchange Commission, är du välkommen att använda,SANT eller bara lämna, FALSE off. Men varje VLOOKUP som du någonsin skapar ska sluta på, FALSE eller, 0, 0 är kortare än FALSE, du ska sätta en FALSE där, men en 0 är samma sak som FALSE.
Okej nu, felsökning, första gången, när du gör en hel massa VLOOKUPs är det väldigt normalt att ha ett par # N / As, eller hur? Och jag hittar alltid # N / A genom att gå Data, ZA, som tar # N / As till toppen, precis där, BG33-9, antingen det är ett stavfel eller det är ett helt nytt objekt, okej, och vi har att lista ut det. Så här till höger har jag de nya uppgifterna, jag kommer att klippa ut det och sedan Alt + IED, sätt in dessa celler i mitten, det behöver inte vara alfabetiskt, den här tabellen behöver inte sorteras. När du använder FALSE-versionen är tabellen inte - du kan bara lägga den var du vill, jag lade inte i slutet eftersom jag inte behövde skriva om formeln, jag vill bara att formeln ska arbete. Okej, så ett par # N / A, extremt, extremt normalt, men kolla in det här.
När du börjar med svar som fungerar, men då # N / A börjar visas lite ofta, och så småningom visas de hela vägen ner, det är ett säkert tecken på att du inte låste ner tabellreferensen. Okej se, så här rör sig tabellen när jag kopierar formeln ner, höger, och så jag har tur att slå några som var i slutet av listan. Men så småningom kommer jag ner till den punkt där det ser bort här i helt tomma celler och naturligtvis ingenting hittas. Okej, så det är det första du får ett par som fungerar, några # N / A, ett par till som fungerar, och sedan är alla # N / A resten av vägen - säkert tecken på att du inte satte $ in.
Gå bara tillbaka, F2 för redigeringsläge, välj kolon, tryck på F4, som sätter in alla $, dubbelklicka för att skjuta ner det, och saker börjar fungera igen, okej. Nästa, exakt samma formel, formeln är perfekt, BG33-8 ser vi, vi får inget av det rätta. Okej, så jag tittar, BG33-8, hej, där är det, det är precis där, det är i rött, jag borde ha sett det! Så jag kommer till den här på höger sida, jag trycker på F2 och jag tittar på den blinkande införingspunkten och ser, det är precis efter 8, så, och sedan kommer jag hit och jag trycker på F2, det finns några bakre utrymmen där. Detta är väldigt, mycket vanligt tillbaka på COBOLs dagar, de skulle säga "Du vet, titta, vi kommer att ge dig 10 mellanslag för artikelnummer, och om du inte skriver alla 10 mellanslag fyller de mellanslag . ” Och så är detta mycket vanligt,och den stora lösningen här är att bli av med de ledande och efterföljande utrymmena med TRIM-funktionen. Istället för A2 använd TRIM (A2), dubbelklicka för att skjuta ner det, okej, fortfarande är BG33-9 tillbaka i den andra tabellen.
Okej, bara så att du förstår hur TRIM fungerar, så jag skrev ett gäng mellanslag, John, ett gäng mellanslag, Durran och ett gäng mellanslag, och sedan sammanfogade jag ett * före och efter så att du kan se hur det ser ut . När jag ber om TRIM (P4) blir vi av med alla ledande utrymmen, alla bakre utrymmen, och sedan minskas de flera inre utrymmena till ett utrymme. Okej, så att du kan se, visualisera där, att de blir av med ledande och efterföljande utrymmen, alla fördubblade utrymmen i mitten blir ett sådant utrymme. Så TRIM, bra, bra verktyg i din arsenal, okej, här är en annan riktigt vanlig.
Om det inte är de efterföljande utrymmena är det vanligaste jag har sett här vi har sanna siffror, och här har vi siffror lagrade som text. Och VLOOKUP kommer inte att se det som en matchning, även om 4399, detta är ett nummer, det här är text, fungerar inte. Snabbaste sättet att konvertera en textkolumn till siffror, välj kolumnen, tre bokstäver i ordning, alt = "" DEF, och plötsligt börjar våra VLOOKUPs att fungera igen, bra, bra tips från cirka 1500 podcasts sedan. Okej, så det här är de vanligaste VLOOKUP-problemen, antingen har du glömt $, eller så har du efterföljande mellanslag, eller så har du nummer och nummer lagrade som text. Alla dessa tips finns i den här boken "MrExcel XL", klicka på "i" längst upp till höger, du kan köpa boken.
Okej, snabb sammanfattning: VLOOKUP löser många problem om en VLOOKUP börjar fungera men # N / A! blir mer framträdande, du glömde att lägga $ i uppslagstabellen. Om det finns några # N / A, är det bara saker som saknas i tabellen. Om ingen av VLOOKUP: erna fungerar och det är text, kolla efter efterföljande utrymmen, du kan använda TRIM-funktionen. Om du har siffror och siffror lagrade som text, välj någon av kolumnerna, den som har texten, och gör sedan alt = "" DEF till alla dem som går tillbaka till siffrorna.
Okej hej, jag vill tacka dig för att du kom förbi, vi ses nästa gång för en ny netcast från!
Nedladdning fil
Ladda ner exempelfilen här: Podcast2027.xlsx
