Excels VLOOKUP-funksjon brukes til å finne og returnere informasjon fra en tabell med data basert på en oppslagsverdi du velger.
Vanligvis krever VLOOKUP oppslagsverdi for å være i venstre kolonne i datatabellen, og funksjonen returnerer et annet felt av data som ligger i samme rad til høyre for denne verdien.
Men ved å kombinere VLOOKUP med CHOOSE-funksjonen, kan en venstre oppslag formel opprettes som:
- Tillater oppslagsverdi å være fra en hvilken som helst kolonne i datatabellen
- Returnerer informasjon som ligger i en hvilken som helst kolonne til venstre for oppslagsverdien
Merk: Disse instruksjonene gjelder for Excel-versjoner 2019, 2016, 2013, 2010 og Excel for Office 365.
01 av 05Tutorial: Bruk VLOOKUP og velg funksjoner i en Venstre Oppslag Formel
For å opprette den venstre tittelformel sett i eksempelbildet, bruk formelen:
= FINN.RAD ($ D $ 2, VELG ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)
I dette eksemplet gjør det mulig å finne delen som leveres av de forskjellige selskapene som er oppført i kolonne 3 i datatabellen.
Arbeidet med CHOOSE-funksjonen i formelen er å lure VLOOKUP til å tro at kolonne 3 er kolonne 1. Som et resultat kan selskapets navn brukes som oppslagsverdi for å finne navnet på delen som leveres av hvert selskap.
Oppgi opplæringsdataene
- Skriv inn overskriften Leverandør inn i cellen D1.
- Skriv inn overskriften Del i cellen E1.
- Skriv inn tabellen med data sett i bildet ovenfor i celler D4 til F9
- rader 2 og 3 er tomt for å imøtekomme søkekriteriene og den venstre oppslagsformelen opprettet under denne opplæringen
Åpne dialogboksen VLOOKUP
Selv om det er mulig å skrive formelen direkte i celle F1 i regnearket, har mange mennesker problemer med syntaksen av formelen.
I dette tilfellet er det sikrere å bruke VLOOKUP-dialogboksen. Nesten alle Excel-funksjonene har en dialogboks som lar deg legge inn hver av funksjonens argumenter på en egen linje.
- Klikk på cellen E2 av regnearket. E2 er stedet der resultatene av den venstre oppslagsmetoden vil bli vist.
- Klikk på formler bånd av båndet.
- Klikk på Oppslag og referanse alternativet i båndet for å åpne funksjonsmenyen.
- Klikk på VLOOKUP i listen for å hente funksjonens dialogboks.
Angi argumenter i dialogboksen VLOOKUP
En funksjons argumenter er verdiene som brukes av funksjonen for å beregne et resultat.
I en funksjons dialogboks er navnet på hvert argument på en egen linje etterfulgt av et felt der du skal skrive inn en verdi.
Skriv inn følgende verdier for hver av VLOOKUPs argumenter på den riktige linjen i dialogboksen, som vist i vedlagte bilde.
Oppslagsverdien
Oppslagsverdi er feltet informasjon som brukes til å søke i tabelloppstillingen. VLOOKUP returnerer et annet felt med data fra samme rad som oppslagsverdien.
Dette eksemplet bruker en cellereferanse til stedet der firmaets navn skal legges inn i regnearket. Fordelen med dette er at det gjør det enkelt å endre firmanavnet uten å redigere formelen.
- Klikk på søkeverdi linje i dialogboksen.
- Klikk på cellen D2 å legge til denne cellehenvisningen til søkeverdi linje.
- trykk F4 tast på tastaturet for å gjøre cellehenvisningen absolutt - $ D $ 2.
Absolutt Cell Referanser
Absolutt cellehenvisninger brukes til oppslagsverdien og tabellarrayargumentene for å forhindre feil hvis oppslagsmetoden kopieres til andre celler i regnearket.
Angir VALG-funksjonen
Tabellen array argumentet er blokken av sammenhengende data fra hvilken spesifikk informasjon hentes.
Vanligvis ser VLOOKUP bare til høyre for oppslagsargumentet for å finne data i tabelloppsettet. For å få det til å se til venstre, må VLOOKUP bli lurt ved å omplassere kolonnene i tabelloppstillingen ved hjelp av CHOOSE-funksjonen.
I denne formelen oppnår valg-funksjonen to oppgaver:
- Det oppretter en tabellmatrise som kun er to kolonner brede (kolonner D og F).
- Det endrer høyre til venstre rekkefølge av kolonnene i tabelloppsettet slik at kolonne F kommer først og kolonne D er andre.
Angi funksjoner
Når du skriver inn funksjoner manuelt, må hver av funksjonens argumenter skilles med et komma.
- I dialogboksen VLOOKUP-funksjonen klikker du på tabell linje.
- Skriv inn følgende VELGE funksjon:VELG ({1,2}, $ F: $ F, $ D: $ D)
Kolonneindeksnummer
Normalt indikerer kolonneindeksnummeret hvilken kolonne i tabelloppsettet inneholder dataene du er ute etter. I denne formelen refererer det imidlertid til rekkefølgen på kolonner som er angitt av CHOOSE-funksjonen.
VELG-funksjonen oppretter en tabellmatrise som er to kolonner brede med kolonne F først etterfulgt av kolonne D. Siden informasjonen som søktes - delnavnet - er i kolonne D, må verdien av kolonneindeksargumentet settes til 2.
- Klikk på Col_index_num linje i dialogboksen.
- Skriv a 2 i denne linjen.
Range Lookup
VLOOKUPs argument for Range_lookup er en logisk verdi (kun TRUE eller FALSE) som angir om du vil ha VLOOKUP for å finne en eksakt eller omtrentlig samsvar med oppslagsverdien.
- Hvis TRUE eller hvis dette argumentet utelates, returnerer VLOOKUP enten et eksakt samsvar til Lookup_value eller, hvis en eksakt kamp ikke blir funnet, returnerer VLOOKUP neste største verdi. For formelen for å gjøre dette, må dataene i den første kolonnen i Table_array sorteres i stigende rekkefølge.
- Hvis FALSE, bruker VLOOKUP bare en eksakt samsvar til Lookup_value. Hvis det er to eller flere verdier i den første kolonnen i Table_array som samsvarer med oppslagsverdien, blir den første verdien funnet. Hvis en eksakt kamp ikke ble funnet, returneres en # N / A-feil.
I denne opplæringen, siden vi leter etter et bestemt delnavn, er Range_lookup satt til Falsk slik at bare eksakte kamper returneres av formelen.
- Klikk på range_lookup linje i dialogboksen.
- Skriv ordet Falsk i denne linjen for å indikere at vi ønsker VLOOKUP å returnere en eksakt kamp for dataene vi søker.
- Klikk OK for å fullføre venstre oppslag formel og lukk dialogboksen.
- Fordi vi ennå ikke har oppgitt firmanavnet i celle D2, vises en # N / A-feil i celle E2.
Returdata med Venstre Oppslag Formel
For å finne hvilke selskaper som leverer hvilke deler, skriv et firma navn i celle D2 og trykk på TAST INN tast på tastaturet.
Delnavnet vises i celle E2.
- Klikk på cellen D2 i regnearket ditt.
- Type Gadgets Plus inn i cellen D2 og trykk på TAST INN tast på tastaturet.
- Teksten "Gadgets" - delen som leveres av selskapet Gadgets Plus - skal vises i celle E2.
Test oppslagstabellen videre ved å skrive andre firmanavn i celle D2, og det tilsvarende delnavnet skal vises i celle E2.
Hvis en feilmelding som # N / A vises i celle E2, kontroller du for stavefeil i celle D2.
04 av 05Opprette en to-kolonne tabellarray
Syntaxen for CHOOSE-funksjonen er:
= VELG (Index_nummer, Verdi1, Verdi2, … Verdi254)
VELG-funksjonen returnerer vanligvis en verdi fra listen over verdier (Verdi1 til Verdi254) basert på indeksnummeret som er angitt.
Hvis indeksnummeret er 1, returnerer funksjonen Verdi1 fra listen; hvis indeksnummeret er 2, returnerer funksjonen verdi2 fra listen og så videre.
Når flere indeksnumre er oppgitt, returnerer funksjonen flere verdier i ønsket rekkefølge. Å VELGE Å returnere flere verdier, gjøres ved å opprette en matrise.
For å angi en matrise, omgir du tallene som er inntatt med krøllede bøyler eller braketter. To tall er oppgitt for indeksnummeret:{ 1,2 }.
Det bør bemerkes at CHOOSE ikke er begrenset til å lage en to-kolonne tabell. Ved å inkludere et ekstra tall i gruppen - for eksempel {1,2,3} - og et ekstra område i verdien argumentet, velger CHOOSE en tre-kolonne tabell.
Ytterligere kolonner tillater deg å returnere annen informasjon med den venstre oppslagsformelen ved å endre VLOOKUPs kolonneindeksnummerargument til tallet i kolonnen som inneholder ønsket informasjon.
05 av 05Endre rekkefølgen på kolonner med VELG-funksjonen
I VELG-funksjonen som brukes i denne formelen:
VELG ({1,2}, $ F: $ F, $ D: $ D)
området for kolonne F er oppført før kolonne D.
Siden CHOOSE-funksjonen setter VLOOKUPs tabelloppsett (datakilden for den funksjonen), byttes rekkefølgen av kolonnene i VELG-funksjonen videre til VLOOKUP.
Når det gjelder VLOOKUP, er tabelloppsettet kun to kolonner brede med kolonne F til venstre og kolonne D til høyre. Siden kolonne F inneholder navnet på firmaet vi vil søke etter, og siden kolonne D inneholder delnavnene, kan VLOOKUP utføre sine vanlige oppslagstjenester ved å finne data som er plassert til venstre for oppslagsverdien.
Som et resultat kan VLOOKUP bruke firmanavnet til å finne den delen de leverer.
