Ved å kombinere Excels VLOOKUP-funksjon med COLUMN-funksjonen kan du lage en oppslagsformel som returnerer flere verdier fra en enkelt rad i en database eller tabell med data. Finn ut hvordan du lager en oppslagsformel som returnerer flere verdier fra én enkelt datapost.
Instruksjonene i denne artikkelen gjelder for Excel 2019, 2016, 2013, 2010; og Excel for Microsoft 365.
bunnlinjen
Oppslagsformelen krever at COLUMN-funksjonen er nestet inne i VLOOKUP. Nesting av en funksjon innebærer å angi den andre funksjonen som et av argumentene for den første funksjonen.
Skriv inn opplæringsdata
I denne opplæringen legges COLUMN-funksjonen inn som kolonneindeksnummerargument for VLOOKUP. Det siste trinnet i opplæringen innebærer å kopiere oppslagsformelen til flere kolonner for å hente tilleggsverdier for den valgte delen.
Det første trinnet i denne opplæringen er å legge inn dataene i et Excel-regneark. For å følge trinnene i denne opplæringen, skriv inn dataene vist i bildet nedenfor i følgende celler:
- Skriv inn det øverste dataområdet i cellene D1 til G1.
- Skriv inn det andre området i cellene D4 til G10.
Søkekriteriene og oppslagsformelen som er opprettet i denne opplæringen, legges inn i rad 2 i regnearket.
Denne opplæringen inkluderer ikke den grunnleggende Excel-formateringen vist i bildet, men dette påvirker ikke hvordan oppslagsformelen fungerer.
Opprett et navngitt område for datatabellen
Et navngitt område er en enkel måte å referere til en rekke data i en formel. I stedet for å skrive cellereferansene for data, skriv inn navnet på området.
En annen fordel med å bruke et navngitt område er at cellereferansene for dette området aldri endres selv når formelen kopieres til andre celler i regnearket. Områdenavn er et alternativ til å bruke absolutte cellereferanser for å forhindre feil ved kopiering av formler.
Utvalgsnavnet inkluderer ikke overskriftene eller feltnavnene for dataene (som vist i rad 4), bare dataene.
-
Høypunkt celler D5 til G10 i regnearket.
Image -
Plasser markøren i navneboksen over kolonne A, skriv Table, og trykk deretter Enter. Cellene D5 til G10 har områdenavnet Tabell.
Image - Områdenavnet for VLOOKUP-tabellmatriseargumentet brukes senere i denne opplæringen.
Åpne VLOOKUP-dialogboksen
Selv om det er mulig å skrive oppslagsformelen direkte inn i en celle i et regneark, synes mange det er vanskelig å holde syntaksen rett - spesielt for en kompleks formel som den som brukes i denne opplæringen.
Som et alternativ, bruk VLOOKUP-funksjonsargument-dialogboksen. Nesten alle Excels funksjoner har en dialogboks der hvert av funksjonens argumenter legges inn på en egen linje.
-
Velg celle E2 i regnearket. Dette er stedet der resultatene av den todimensjonale oppslagsformelen vises.
Image -
På båndet, gå til Formulas-fanen og velg Lookup & Reference.
Image -
Velg VLOOKUP for å åpne Function Arguments-dialogboksen.
Image - Funksjonsargumenter-dialogboksen er der parametrene til VLOOKUP-funksjonen legges inn.
Angi oppslagsverdiargumentet
Vanligvis samsvarer oppslagsverdien med et datafelt i den første kolonnen i datatabellen. I dette eksemplet refererer oppslagsverdien til navnet på delen du vil finne informasjon om. De tillatte datatypene for oppslagsverdien er tekstdata, logiske verdier, tall og cellereferanser.
Absolute cellereferanser
Når formler kopieres i Excel, endres cellereferanser for å gjenspeile den nye plasseringen. Hvis dette skjer, endres D2, cellereferansen for oppslagsverdien, og skaper feil i cellene F2 og G2.
Absolutt cellereferanser endres ikke når formler kopieres.
For å forhindre feil, konverter cellereferansen D2 til en absolutt cellereferanse. For å opprette en absolutt cellereferanse, trykk på F4-tasten. Dette legger til dollartegn rundt cellereferansen, for eksempel $D$2.
-
I funksjonsargument-dialogboksen plasserer du markøren i tekstboksen lookup_value. Velg deretter celle D2 i regnearket for å legge til denne cellereferansen til lookup_value. Celle D2 er der delnavnet skal skrives inn.
Image -
Uten å flytte innsettingspunktet, trykk F4-tasten for å konvertere D2 til den absolutte cellereferansen $D$2.
Image - La VLOOKUP-funksjonsdialogboksen være åpen for neste trinn i opplæringen.
Skriv inn tabellargumentet
En tabellmatrise er tabellen med data som oppslagsformelen søker for å finne informasjonen du ønsker. Tabellmatrisen må inneholde minst to kolonner med data.
Den første kolonnen inneholder oppslagsverdiargumentet (som ble satt opp i forrige avsnitt), mens den andre kolonnen søkes etter oppslagsformelen for å finne informasjonen du spesifiserer.
Tabellarray-argumentet må enten angis som et område som inneholder cellereferansene for datatabellen eller som et områdenavn.
For å legge til tabellen med data til VLOOKUP-funksjonen, plasser markøren i table_array tekstboksen i dialogboksen og skriv Tablefor å angi områdenavnet for dette argumentet.
Nest the COLUMN-funksjonen
Vanligvis returnerer VLOOKUP bare data fra én kolonne i en datatabell. Denne kolonnen er satt av argumentet for kolonneindeksnummer. I dette eksemplet er det imidlertid tre kolonner, og kolonneindeksnummeret må endres uten å redigere oppslagsformelen. For å oppnå dette, nester du COLUMN-funksjonen inne i VLOOKUP-funksjonen som Col_index_num-argumentet.
Når funksjoner bygges inn, åpner ikke Excel dialogboksen for den andre funksjonen for å legge inn argumentene. KOLONNE-funksjonen må legges inn manuelt. COLUMN-funksjonen har bare ett argument, Reference-argumentet, som er en cellereferanse.
COLUMN-funksjonen returnerer nummeret på kolonnen som er oppgitt som Reference-argument. Den konverterer kolonnebokstaven til et tall.
For å finne prisen på en vare, bruk dataene i kolonne 2 i datatabellen. Dette eksemplet bruker kolonne B som referanse for å sette inn 2 i Col_index_num-argumentet.
-
I dialogboksen Function Arguments, plasser markøren i Col_index_num tekstboksen og skriv COLUMN(. (Pass på å inkludere den åpne runde parentesen.)
Image -
I regnearket, velg celle B1 for å angi den cellereferansen som referanseargument.
Image - Skriv inn en sluttende runde-parentes for å fullføre COLUMN-funksjonen.
Angi VLOOKUP Range Lookup Argument
VLOOKUPs Range_lookup-argument er en logisk verdi (TRUE eller FALSE) som indikerer om VLOOKUP skal finne et eksakt eller omtrentlig samsvar med Lookup_value.
- TRUE eller utelatt: VLOOKUP returnerer en nær match til Lookup_value. Hvis et eksakt samsvar ikke blir funnet, returnerer VLOOKUP den nest største verdien. Dataene i den første kolonnen i Table_array må sorteres i stigende rekkefølge.
- FALSE: VLOOKUP bruker et eksakt samsvar med Lookup_value. Hvis det er to eller flere verdier i den første kolonnen i Table_array som samsvarer med oppslagsverdien, brukes den første verdien som ble funnet. Hvis et eksakt samsvar ikke blir funnet, returneres en N/A-feil.
I denne opplæringen vil spesifikk informasjon om et bestemt maskinvareelement bli sett opp, så Range_lookup er satt til FALSE.
I funksjonsargumenter-dialogboksen, plasser markøren i Range_lookup-tekstboksen og skriv False for å fortelle VLOOKUP å returnere et eksakt samsvar for dataene.
Velg OK for å fullføre oppslagsformelen og lukke dialogboksen. Celle E2 vil inneholde en N/A-feil fordi oppslagskriteriene ikke er lagt inn i celle D2. Denne feilen er midlertidig. Det vil bli rettet når oppslagskriteriene legges til i det siste trinnet i denne opplæringen.
Kopier oppslagsformelen og skriv inn kriterier
Oppslagsformelen henter data fra flere kolonner i datatabellen samtidig. For å gjøre dette må oppslagsformelen ligge i alle feltene du ønsker informasjon fra.
For å hente data fra kolonne 2, 3 og 4 i datatabellen (prisen, delenummeret og leverandørens navn), skriv inn et delnavn som Lookup_value.
Siden dataene er lagt ut i et vanlig mønster i regnearket, kopierer du oppslagsformelen i celle E2 til celler F2 og G2 Etter hvert som formelen kopieres, oppdaterer Excel den relative cellereferansen i COLUMN-funksjonen (celle B1) for å gjenspeile formelens nye plassering. Excel endrer ikke absolutt cellereferanse (som $D$2) og det navngitte området (tabell) når formelen kopieres.
Det er mer enn én måte å kopiere data i Excel, men den enkleste måten er å bruke Fill Handle.
-
Velg celle E2, der oppslagsformelen er plassert, for å gjøre den til den aktive cellen.
Image -
Dra påfyllingshåndtaket over til celle G2. Cellene F2 og G2 viser N/A-feilen som finnes i celle E2.
Image -
For å bruke oppslagsformlene til å hente informasjon fra datatabellen, velg celle D2 i regnearket, skriv Widget, og trykk Enter.
Image Følgende informasjon vises i cellene E2 til G2.
- E2: $14,76 – prisen på en widget
- F2: PN-98769 - delenummeret for en widget
- G2: Widgets Inc. - navnet på leverandøren for widgets
-
For å teste VLOOKUP-matriseformelen, skriv inn navnet på andre deler i celle D2 og observer resultatene i cellene E2 til G2.
Image - Hver celle som inneholder oppslagsformelen inneholder forskjellige data om maskinvareelementet du søkte etter.
VLOOKUP-funksjonen med nestede funksjoner som COLUMN gir en kraftig metode for å slå opp data inne i en tabell ved å bruke andre data som oppslagsreferanse.
