Introduksjon til VLOOKUP -funksjon
VLOOKUP -funksjonen i Excel er uten tvil den mest brukte og snakkede funksjonen. I ethvert jobbintervju som krever Excel -ferdigheter, er dette det mest stilte spørsmålet at hvis du vet hvordan du bruker VLOOKUP -funksjonen.
VLOOKUP -funksjonen er under oppslagskategorien excel -funksjoner. V i VLOOKUP står for vertikal. Funksjonen brukes til å slå opp data som er strukturert vertikalt. For Horisontal oppslag er det en funksjon som kalles HLOOKUP.
Excel VLOOKUP -funksjon ser etter den første matchingen av en gitt verdi kalt oppslagsverdi, i en datatabell og returnerer verdien fra den gitte kolonneindeksen. Kampen kan være omtrentlig eller nøyaktig.
Selv om VLOOKUP er enkel å bruke, har den sine egne begrensninger og baktepper. Vi vil diskutere alt, inkludert styrker og svakheter ved funksjonen.
Syntaks:
= VLOOKUP (oppslagsverdi, tabellarray, col_index_number, [range_lookup])
Hva er oppslagsverdi
Oppslagsverdi: Det er verdien du vil se etter i en tabellmatrise.
For eksempel, hvis du leter etter rull nr. 110 i elevens bord, så er det 110 er oppslagsverdi.
Tabell_array: Tabellen du vil se etter oppslagsverdi fra.
For eksempel, hvis du ser 110 i tabellmatrisen B5: E17. Deretter B5: E17 er tabellen din.
col_index_number: Kolonnenummeret i tabellmatrise som du vil hente resultater fra.
For eksempel hvis det er i tabellmatrise B5: E17 du vil få verdi fra kolonne D da vil kolonneindeksen din være 3 (teller fra B til D (B, C, D)).
[range_lookup]: FALSK hvis du vil søke etter eksakt verdi, SANN hvis du vil ha en omtrentlig samsvar.
Slik bruker du VLOOKUP -funksjonen
Når du har strukturerte data og du vil lete etter data i dataene, er VLOOKUP -funksjonen løsningen.
VLOOKUP matchende moduser
VLOOKUP har to matchningsmoduser, tilnærmet samsvar og eksakt samsvar. Som standard utfører VLOOKUP en omtrentlig kamp. Men hvis du vil tvinge VLOOKUP -funksjonen til å gjøre en eksakt match, kan du også gjøre det. Som oftest tror jeg og jeg sterkt på at andre også prøver å gjøre en eksakt match ved å bruke VLOOKUP -funksjonen. Jeg forstår ikke hvorfor excel -utviklere tror at brukere stort sett vil bruke den omtrentlige kampen.
1. VLOOKUP Omtrentlig kamp
= VLOOKUP (oppslagsverdi, tabellarray, col_index_number, 1)
I VLOOKUP omtrentlig samsvar, hvis en verdi ikke blir funnet, vil siste verdi som er mindre enn oppslagsverdi samsvarer og verdien fra gitt kolonneindeks returneres.
VLOOKUP som standard samsvarer omtrentlig, hvis vi utelater variabel for oppslag i rekkevidde. En omtrentlig kamp er nyttig når du vil gjøre en omtrentlig kamp og når du har tabellen din sortert i stigende rekkefølge.
Vlookup ser etter verdien, og hvis den ikke finner verdien i tabellmatrisen, samsvarer den med verdien som er mindre enn den verdien i tabellmatrisen. La oss forstå det ved et eksempel.
Eksempel 1
Vi har en studentliste her. Hver elev har fått noen karakterer i en test. Nå vil jeg gi karakter i henhold til karakterene deres.
Enhver student som scoret mindre enn 40 bør merkes F, student som scorer mellom 40 og 60 bør merkes C, og så videre som vist på bildet nedenfor.
Vi ville skrive denne VLOOKUP -formelen i E2 og dra den ned.
= VLOOKUP (D2, $ H $ 2: $ I $ 6,2, TRUE)
Hvordan det fungerer?
I eksemplet ovenfor er oppslagsverdiene våre i kolonne D, fra D2. Tabellen Array er H2: I6 (merk at den er sortert i stigende rekkefølge og absolutt). Kolonnen vi får data fra er 2. Og samsvartype vi har definert omtrentlig ved å passere TRUE (ingenting og 1 vil bety det samme).
Så la oss undersøke oppslagsverdi D2 som inneholder 40.
- VLOOKUP ser etter 40 i første kolonne (H) i tabellarray H2: I6.
- Den finner 40 i andre posisjon i celle H3.
- Nå går den til andre kolonne fra H3 til I3 og returnerer D.
I dette tilfellet fant vlookup den eksakte matchen. La oss se neste sak.
Den andre oppslagsverdiene D3 som inneholder 36.
- VLOOKUP ser etter 36 i første kolonne i tabellmatrise H2: I6.
- VLOOKUP fant ikke 36 hvor som helst i første kolonne.
- Siden VLOOKUP ikke kunne finne 36, den samsvarer med den sist funnet verdien som er mindre enn oppslagsverdien.
- Den første verdien som er mindre enn 36 er 0, derfor returnerer den F som er relatert til 0.
Dette skjer for hvert tilfelle her. For 92 er siste verdi som er mindre enn 92 90. Derfor returneres A for omtrentlig samsvar.
2. VLOOKUP Exact Match
Denne mest brukte formen for VLOOKUP og kan også være mest nyttig. Når du vil lete etter eksakt verdi i første kolonne i tabellmatrisen, bruker du eksakt samsvar. For eksempel hvis du søker etter en ID enn du helst vil ha en eksakt samsvar i stedet for en verdi som er mindre enn denne verdien.
For å tvinge VLOOKUP til en eksakt match i excel, sender vi 0 eller FALSE som parameter_lookup.
= VISNING (oppslagsverdi, tabellrute, kol_indeks_nummer, 0)
Hvis verdien ikke finnes i den første kolonnen i tabellmatrisen, returnerer formelen #N/A -feil.
La oss se et eksempel.
Eksempel 2
I dette eksemplet vil jeg bare skrive rullelement av studenter i A2 og i B2 vil jeg hente Elevens navn og i C2 hans Karakter. Enkel. Tabelloppstilling er B5: D17. Hvorfor?
Så for å gjøre dette, skriver du disse to formlene i cellen i henholdsvis celle B2 og C2.
= VLOOKUP (A2, $ B $ 5: $ E $ 17,2,0)
= VLOOKUP (A2, $ B $ 5: $ E $ 17,3,0)
Uansett hvilken ID du skriver i celle A2, vil VLOOKUP -funksjonen se etter eksakt samsvar i B -kolonnen og returnere verdien fra gitt col_index.
Hvordan fungerer eksakt match VLOOKUP?
Exact Match VLOOKUP i excel er enkelt. Den ser etter oppslagsverdien i første kolonne i gitt tabellarray og returnerer verdi fra verdi fra gitt kolonneindeksnummer. Hvis verdien ikke blir funnet, returnerer VLOOKUP #N/A feil.
Beste bruk av VLOOKUP -funksjon
Vlookup har mange bruksområder. Jeg skal diskutere noen av de mest nyttige bruksområdene.
3. Bruk Excel VLOOKUP for gradering i stedet for kompleks nestet IF -funksjon
Bruk omtrentlig samsvar for VLOOKUP for å erstatte nestet IF -funksjon. I eksempel 1 så vi hvordan vi kan bruke vlookup til å rangere elever i henhold til karakterene deres.
Den omtrentlige matchningen VLOOKUP er raskere enn nestet hvis den bruker binært søk internt.
4. Bruk VLOOKUP for å hente data
Denne vanligste oppgaven som enkelt kan utføres ved å bruke VLOOKUP -funksjonen i Excel. I eksempel 2 gjorde vi dette for å hente elevdata ved hjelp av nummeret deres.
5. Sjekk om en verdi er i en post ved hjelp av Vlookup.
VLOOKUP kan brukes til å sjekke om en verdi er i en liste eller ikke.
I eksempel 2 når vi skriver 152, returnerer det #N/A feil. Hvis VLOOKUP returnerer #N/A, betyr det at den ikke fant den oppgitte verdien noe sted i gitte data.
Jeg har utarbeidet en kort artikkel om dette Du kan lese det her.
Bruk en VLOOKUP -formel for å sjekke om det finnes en verdi
Få kolonneindeks automatisk
Jeg tilhører den gruppen mennesker, som ikke ønsker å gjenta samme oppgave igjen og igjen. Så jeg irriterer meg over å endre col_index nummer igjen og igjen, og jeg unngår det alltid. Egentlig unngår jeg noen form for redigering i formlene mine når jeg har skrevet det, med mindre og til det ikke er noe annet alternativ.
Kolonneindeksen kan beregnes automatisk i Excel. Det er flere måter å gjøre det på. La oss se noen av dem her.
6. Bruk VLOOKUP -funksjon med MATCH -funksjon
Så som vi vet at MATCH -funksjonen returnerer funnet indeks for matchet verdi.
Så hvis vi har nøyaktig samme overskrifter på oppslagsstedet som kildedataene, enn vi kan bruke for å få col_index. Hvordan? La oss se…
I dataene nedenfor vil vi bare skrive rullnummer i G2 og ønsker å hente studentnavn, karakterer og karakter i H2, uansett hvilken overskrift vi skriver der.
Skriv denne formelen i celle H2
= VISNING (G2, B2: E14, MATCH (H1, B1: E1,0), 0)
Når du endrer overskrift i H1, vil verdien i H2 bli vist fra den kolonnen.
Du kan bruke en rullegardinmeny ved hjelp av datavalidering som har alle overskriftene fra tabellen, for å gjøre den mer brukervennlig.
Her gjør VLOOKUP eksakt match. Nå gjør VLOOKUP sine vanlige ting med å trekke data. Magien utføres av MATCH -funksjonen ved col_index -posisjon.
Her ser VLOOKUP etter en verdi i G2 i første kolonne i tabellmatrise B2: E14. Nå på stedet for col_index har vi MATCH (H1, B1: E1,0).
MATCH -funksjonen ser etter hvilken som helst verdi i H1 i området B1: E1 og returnerer indeks for samsvarende verdi.
For eksempel når vi skriver student i H1 ser det etter det innen rekkevidde B1: E1. Den finnes i C2. derav returnerer den 2. Hvis vi skriver Karakter det vil returnere karakteren til studenten.
Vær oppmerksom på at når vi skriver Region, returnerer den #I/A. Fordi det er utenfor rekkevidden av tabellen. Vi vil snakke om det sistnevnte i artikkelen.
Dette er den beste måten å gjøre VLOOKUP automatisk. Det er andre metoder, men de er ikke så fleksible enn dette.
7. Bruk VLOOKUP -funksjon med COLUMN -funksjon
Kolonnefunksjonen returnerer kolonnenummeret til den medfølgende referansen. Og hva trenger vi for å hente data ved hjelp av VLOOKUP? Col_index. Så ved å bruke COLUMN -funksjonen kan vi selvfølgelig hente data fra alle datasett.
La oss ta eksemplet ovenfor. Men nå må vi hente hele data. Ikke bare en kolonne.
Skriv denne formelen i H2 og kopier i I2 og J2.
= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, COLUMN (B1), 0)
Dette vil gi deg et dynamisk resultat. Hvordan? La oss se.
VLOOKUP fungerer som vanlig. På column_index har vi skrevet KOLONNE (B1), som vil oversette til 2, siden vi har elevnavn i 2 -kolonne fra B -kolonne, returnerer det navnet på studenten.
Viktig: Dette fungerer fordi vi har tabellen vår fra B -kolonnen. Hvis bordet ditt er midt på arket, må du trekke fra eller legge til noen tall slik at det samsvarer med kravet ditt.
For eksempel, hvis tabellen ovenfor startet fra C1 i samme struktur, fungerte alle formlene nedenfor bra.
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, COLUMN (B1), 0)
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, COLUMN (C1) -1,0)
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, COLUMN (A1) +1,0)
8. VLOOKUP for å slå sammen data
I de fleste databaser er det en spørring som smelter sammen med tabeller, hovedsakelig kalt en forespørselsforespørsel, men MS Excel i ingen database, derfor har den ikke en forespørsel. Men dette betyr ikke at vi ikke kan bli med i to tabeller hvis vi har noen vanlige kolonner i to tabeller.
Så når du får noen data fra to avdelinger i samme selskap, vil du slå dem sammen for å analysere disse dataene.
For eksempel, når du får karakterer av studenter fra lærere og deretter oppmøterekord fra administrativ avdeling, vil du se hvilken elev som fikk hvilken karakter og hva som er hans deltakelsesprosent, på ett sted.
Vi må slå dem sammen i tabellen nedenfor.
Merker, vi må komme fra Marks Table, og Oppmøte fra Oppmøtedata.
Skriv nedenfor formler i celle D19 for å få merker og dra ned.
= VLOOKUP (B19, $ B $ 2: $ D $ 15,3,0)
Skriv under formelen i celle E19
= VLOOKUP (B19, $ G $ 3: $ I $ 15,3,0)
Og vi har slått sammen to bord til ett bord. Du kan selvfølgelig legge til mer enn to bord. Du trenger bare å hente data fra alle tabellene du ønsker på ett sted ved å bruke VLOOKUP.
Beste praksis for VLOOKUP
Så, alle eksemplene ovenfor brukte vi nettopp vlookup med rådata. I alle eksemplene ovenfor var vi forsiktige med dataene vi brukte. Vi måtte være forsiktige mens vi ga referanser til tabelloppstillinger. Men det er visse måter som kan redusere denne innsatsen mye.
9. Bruk Absolute References med VLOOKUP
Du har kanskje lagt merke til at jeg i noen artikler har brukt absolutte områder (område $ -tegn). Absolutt raseri brukes når vi ikke vil at rekkevidde skal endres mens du kopierer formelcelle til andre celler.
For eksempel, hvis jeg har = VLOOKUP (G2,B2: E14, 2,0), 0) i celle H2, og hvis jeg kopierer eller drar den ned i celle H3, vil formelen endres til = VLOOKUP (G3,B3: E15, 2,0). Alle referanser endres relativt. Her vil vi kanskje endre referansen til oppslagsverdi, men ikke tabellmatrisen. Fordi B2 kommer ut eller strekker seg, og vi kanskje alltid vil ha det i bordbordene våre.
Så, for å forhindre at vi bruker absolutte referanser. Vi gjør disse områdene absolutte som vi ikke ønsker å endre, som tabellarray i H2, = VLOOKUP (G2,$ B $ 2: $ E $ 14, 2,0), 0). Når du kopierer H2 i H3 vil formelen være = VLOOKUP (G3,$ B $ 2: $ E $ 14, 2,0), 0). Vær oppmerksom på at oppslagsverdien er endret, men ikke tabell_array.
10. Bruk navngitte områder med VLOOKUP
I eksemplet ovenfor brukte vi absolutt referanse for å fikse table_array. Det ser ganske uklart og vanskelig å lese. Hva om du bare kan skrive 'data' på stedet for $ B $ 2: $ E $ 14 og det ville referere til $ B $ 2: $ E $ 14 bestandig. Det vil gjøre det enda enklere å lese og skrive formelen.
Så bare velg området og gi det navnet "data".
For å navngi et område, velg området. Høyreklikk, klikk på Definer navn og navngi området du vil ha. Nå bare = VLOOKUP (G3,data,2,0), 0) formelen fungerer nøyaktig som tidligere.
Du kan lese mer om navngitte områder i Excel. Jeg har forklart hvert eneste aspekt av det navngitte området her.
11. Jokertegn for delvis matching ved hjelp av VLOOKUP
VLOOKUP tillater delvis matching. Ja, mens du gjør nøyaktig samsvar med VLOOKUP, kan du bruke jokertegnoperatører til å gjøre delvis matching. For eksempel, hvis du vil slå opp en verdi som starter med "Gil", kan du bruke jokertegnoperatør * med "Gil" som "Gil *". La oss se et eksempel.
Her vil jeg få merker av studenter hvis navn begynner med Gil. For å få det, vil jeg skrive formelen nedenfor.
= VLOOKUP ("*Gill", C2: D14,2,0)
Det er to jokertegn som er tilgjengelige i excel for bruk med VLOOKUP -funksjon.
Stjerne (*) jokertegn. Dette brukes når brukeren ikke vet hvor mange tegn det er og bare kjenner noen samsvarende tegn. For eksempel hvis brukeren kjenner et navn som begynner med "Sm", vil han skrive "Sm*". Hvis brukeren vet at en oppslagsverdi som ender med “123”, skriver han “*123”. (jokertegn fungerer bare med tekster). Og om han vet at “se” kommer i en tekst, så skriver han “*se*”.
Spørsmålstegn ("?"). Dette brukes når brukeren kjenner antall tegn som mangler. For eksempel, hvis jeg vil slå opp en verdi som har 4 tegn i den, men jeg vet ikke hva de er, vil jeg bare skrive "????" på stedet for oppslagsverdien.
Vlookup returnerer den respektive verdien av først funnet verdi som har nøyaktig fire tegn i den.
Viktig: Wild card fungerer bare med tekstverdier. Konverter tall til tekst hvis du vil bruke jokertegn med dem.
Svakhetene ved VLOOKUP -funksjonen
VLOOKUP er virkelig en kraftig og enkel funksjon for å hente verdier, men det er mange områder hvor VLOOKUP bare ikke er så nyttig. Hvis du jobber med excel, må du også kjenne dem og hvordan du gjør en oppgave som VLOOKUP ikke kan utføre.
12. Kan ikke hente verdi fra venstre i Table_Array
Den største feilen med VLOOKUP -funksjonen er at den ikke kan hente verdi fra venstre i tabellmatrisen. VLOOKUP bare oppslag rett oppslagsverdien.
Dette er fordi VLOOKUP søker etter gitt oppslagsverdi i den første kolonnen av gitt bordmatrise. Det vil aldri returnere en verdi fra utsiden av bordet. Og hvis du prøver å beholde venstre kolonne i tabellmatrisen, blir den den første kolonnen i tabellmatrisen, derfor vil oppslagsverdien bli søkt i det området. Som sannsynligvis vil generere feil.
For å slå opp verdier fra venstre for en oppslagsverdi bruker vi i stedet INDEX-MATCH. INDEX-MATCH-funksjonen kan slå opp verdier fra en hvilken som helst kolonne på arket. Faktisk er oppslagsområdet og området du vil slå opp verdier fra helt uavhengige, noe som gjør indeksmatch svært tilpassbar.
VLOOKUP returnerer #N/A selv om oppslagsverdi eksisterer.
#I/A -feil returneres av VLOOKUP -funksjonen når oppslagsverdien ikke blir funnet. Du kan synes det er irriterende når det finnes en verdi i området, men VLOOKUP returnerer #N/A feil.
14. Når tall er formatert som tekst
Dette skjer for det meste når tallene er formatert som tekst. Når du finner bruk av CTRL+F -kommandoen vil excel finne den, men når du prøver å bruke VLOOKUP, returnerer den #N/A. Denne oppgaven er gitt i de fleste intervjuer som lure spørsmål.
På bildet ovenfor kan du se at 101 er der, men formelen returnerer en feil. Hvordan takler du det? Vel, det er to metoder.
1.Konverter tekst til tall i data
Du kan se den grønne koden i venstre øvre hjørne i rollno -kolonnen. De indikerer at noe er uvanlig i verdien av celle. Når du velger cellen, vil det vise at tallet er formatert som tekst.
Når du klikker på utropstegnet (!), Vil det vise deg alternativet Konverter til nummer. Velg alle cellene og klikk på dette alternativet. Alle verdier vil bli konvertert til tekst.
2. Konverter oppslagnummeret til tekst i formelen
I eksemplet ovenfor mutert vi originaldata. Du vil kanskje ikke endre noe i originaldataene. Så du vil gjøre dette i formelen i stedet. For å endre nummer til tekst i VLOOKUP -formel, skriv dette.
= OPPLYSNING (TEKST (G2, "0"), B2: D14,2,0)
Her endrer formelen dynamisk tall til tekst og matcher det deretter med gitt område.
15. Tekst med ledende og etterfølgende mellomrom
Noen data fra operatører av dataoppføring og data fra internett er ikke rene. De kan ha etterfølgende mellomrom eller noen tegn som ikke kan skrives ut. Når du søker etter disse verdiene, kan du se en #N/A -feil. For å unngå dette må du først rense dataene dine. Bruk TRIM -funksjonen for å fjerne mellomrom. TRIM fjerner et hvilket som helst antall ledende eller etterfølgende mellomrom fra tekst.
Så jeg foreslår at du legger til en ny kolonne og får rensede data der. Verdi lim den inn. Hvis du vil, kan du bli kvitt den opprinnelige kolonnen.
= TRIM (RENGJØR (tekst))
Håndteringsfeil ved VLOOKUP
Som du vet, finner VLOOKUP ikke en verdi, den returnerer en #N/A -feil. Det er greit å få #N/A -feil, kanskje du noen ganger har tenkt å få det, for eksempel når du vil sjekke om verdien allerede finnes i listen eller ikke. Det #N/A betyr at verdien ikke er der.
Men #N/A ser stygg ut. Hva med å få en brukervennlig utgang, for eksempel "Id ikke funnet" eller "Kunden ble ikke funnet". Vi kan bruke IFERROR med VLOOKUP -funksjon for å unngå #N/A.
Bruk formelen nedenfor for å fange feil i VLOOKUP.
= IFERROR (VLOOKUP (150, B2: E14,2,0), "Roll no not found")
16. VLOOKUP med MULTIPLE Criterias
VLOOKUP kan ikke fungere med flere kriterier.Ja, du kan bare ha ett kriterium for å slå opp verdier ved å bruke VLOOKUP -formelen.
For eksempel hvis du har fornavn og etternavn i to separate kolonner.
Og nå, hvis du vil slå opp verdi hvis fornavn er John og etternavn Dave, kan du ikke gjøre det normalt.
Men det er en løsning på dette. Du kan opprette en egen kolonne ved å sammenkoble fornavn og etternavn, og deretter slå opp den for- og etternavnskolonnen.
Det er en INDEX-MATCH-alternativ som kan slå opp flere kriterier uten noen hjelpende spalte.
17. VLOOKUP Henter bare først funnet verdi
Tenk deg at du har noen data i den sentrale regionen. Nå vil du få de første 5 datapostene i den sentrale regionen. VLOOKUP vil bare returnere den første sentrale verdien i alle fem postene. Dette er et stort bakteppe.
Men det betyr ikke at vi ikke kan oppnå dette. Vi kan bruke en kompleks matriseformel som nedenfor.
= INDEKS ($ C $ 2: $ C $ 14, LITEN (HVIS (G2 = $ A $ 2: $ A $ 14, RAD ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1), RAD (1: 1)))
Skriv denne formelen og trykk CTRL+SKIFT+ENTER.
Og det er gjort.
Jeg har forklart det i artikkelen hvordan man LOOKUP flere verdier i Excel.
Så ja, i denne artikkelen har jeg dekket alle mulige aspekter ved VLOOKUP -funksjon i henhold til min kunnskap. Hvis du tror jeg har savnet noe, vennligst gi meg beskjed i kommentarfeltet nedenfor.
Vlookup Topp 5 verdier med dupliserte verdier ved hjelp av INDEX-MATCH i Excel
VLOOKUP Flere verdier
VLOOKUP med Dynamic Col Index
Delvis match med VLOOKUP -funksjon
Bruk VLOOKUP fra to eller flere oppslagstabeller
Vlookup etter dato i Excel
Bruk en VLOOKUP -formel for å sjekke om det finnes en verdi
Hvordan VLOOKUP fra forskjellige Excel -ark
VLOOKUP med tall og tekst
IF, ISNA og VLOOKUP -funksjon
ISNA- og VLOOKUP -funksjon
IFERROR og VLOOKUP -funksjon