Find flere felter med Excel VLOOKUP

Ved at kombinere Excel'sLOPSLAG funktion medKOLONNE funktion kan vi oprette en opsigelsesformel, der giver dig mulighed for at returnere flere værdier fra en enkelt række i en database eller et datatabell.

01 af 10

Returner flere værdier med Excel VLOOKUP

Ved at følge nedenstående trin oprettes opsætningsformlen set i billedet ovenfor, der returnerer flere værdier fra en enkelt datapost.

Opslagsmetoden kræver, at KOLONNE funktionen er indlejret inde i LOPSLAG. Ved at indlæse en funktion involverer du den anden funktion som en af ​​argumenterne for den første funktion.

I denne vejledning, den KOLONNE funktionen vil blive indtastet som kolonne indeks nummer argument for LOPSLAG. Det sidste trin i vejledningen indebærer kopiering af opsætningsformlen til yderligere kolonner for at hente yderligere værdier for den valgte del.

02 af 10

Indtast vejledningsdataene

Det første trin i vejledningen er at indtaste dataene i et Excel-regneark. For at følge trinene i vejledningen skal du indtaste dataene vist i billedet ovenfor i følgende celler:

• Indtast det øverste udvalg af data i celler D1 til G1.
• Indtast det andet område i celler D4 til G10.

Søgekriterierne og opsætningsformlen, der oprettes under denne vejledning, vil blive indtastet i række 2 af regnearket.

Selvstudiet indeholder ikke formateringen set i billedet, men dette påvirker ikke, hvordan opslagsmetoden fungerer. Oplysninger om formateringsindstillinger svarende til dem, der ses ovenfor, er tilgængelige i denne grundlæggende Excel-formateringstutorial.

03 af 10

Oprettelse af et navngivet område for datatabellen

Et navngivet interval er en nem måde at henvise til en række data i en formel. I stedet for at skrive i cellehenvisningerne for dataene, kan du bare skrive navnet på intervallet.

En anden fordel ved at bruge et navngivet interval er, at cellehenvisningerne for dette interval aldrig ændres, selv når formlen kopieres til andre celler i regnearket. Områdenavne er derfor et alternativ til at bruge absolutte cellehenvisninger for at forhindre fejl, når man kopierer formler.

Områdenavnet indeholder ikke overskrifter eller feltnavne til dataene (række 4), men kun selve dataene.

1. Fremhæv celler D5 til G10 i regnearket for at vælge dem.
2. Klik på Navn Box placeret ovenfor kolonne A.
3. Type Bord ind i Navn Box.
4. Tryk på Gå ind tast på tastaturet.
5. Celler D5 til G10 Nu har serienavnet af Bord. Vi vil bruge navnet på LOPSLAG tabel array argument senere i vejledningen.

04 af 10

Åbning af VLOOKUP-dialogboksen

Selvom det er muligt at skrive vores opkøbsformel direkte i en celle i et regneark, kan det være svært for mange at holde syntaksen lige - især for en kompleks formel som den, vi bruger i denne vejledning.

Et alternativ, i dette tilfælde, er at bruge LOPSLAG dialog boks. Næsten alle Excel's funktioner har en dialogboks, der giver dig mulighed for at indtaste hver af funktionsens argumenter på en separat linje.

Tutorial trin

1. Klik på celle E2 af regnearket - den placering, hvor resultaterne af den todimensionelle opslagsformel vil blive vist.
2. Klik på Formulas fanen af bånd.
3. Klik på Opslag og reference mulighed i bånd for at åbne funktionen drop-down.
4. Klik på LOPSLAG i listen for at åbne funktionens dialogboks.

05 af 10

Indtast Argument for opslagsværdi

Normalt er det kig op værdi matcher et felt af data i den første kolonne i datatabellen. I vores eksempel er kig op værdi henviser til navnet på hardwaredelen, som vi ønsker at finde information om. De tilladte typer af data til kig op værdi er tekstdata, logiske værdier, tal og cellehenvisninger.

Absolutte Cell Referencer

Normalt, når formler kopieres i Excel, ændres cellehenvisninger for at afspejle deres nye placering. Hvis dette sker, D2, cellehenvisningen til kig op værdi, vil ændre sig, da formlen er kopieret, der skaber fejl i celler F2 og G2.

Absolutte cellehenvisninger ændres ikke, når formlerne kopieres.

For at forhindre fejlene konverterer vi cellehenvisningen D2 ind i en absolut celle reference. Absolutte cellehenvisninger oprettes ved at trykke på F4 tast på tastaturet. Hvis du gør det, tilføjer dollartegn omkring cellehenvisningen som f.eks $ D $ 2.

1. Klik på opslagsværdi linje i dialogboksen.
2. Klik på celle D2 at tilføje denne cellehenvisning til opslagsværdi line - dette er cellen hvor vi vil skrive navnet på navnet, som vi søger information om
3. Uden at flytte indsætningspunktet skal du trykke på F4 tast på tastaturet for at konvertere D2 ind i den absolutte cellehenvisning $ D $ 2.
4. Forlad LOPSLAG funktionsdialogboksen åbnes til næste trin i vejledningen.

06 af 10

Indtastning af tabellen Array Argument

Tabellen array er datatabellen, som opslagsformlen søger for at finde de oplysninger, vi ønsker.Tabellen array skal indeholde mindst to kolonner af data.

Den første kolonne indeholder argumentet for opslagsværdi (foregående trin i vejledningen), mens den anden kolonne vil blive søgt af opslagsformlen for at finde de oplysninger, vi angiver.

Tabellen array argumentet skal indtastes som enten et interval indeholdende cellehenvisninger for datatabellen eller som et intervalnavn.

1. Klik på tabelmatrix linje i dialogboksen.
2. Type Bord for at indtaste intervalnavnet for dette argument.
3. Forlad LOPSLAG funktionsdialogboksen åbnes til næste trin i vejledningen.

07 af 10

Nulstilling af COLUMN-funktionen

Normalt LOPSLAG Returnerer kun data fra en kolonne i en datatabel, og denne kolonne er indstillet af kolonne indeksnummer argument.

I dette eksempel har vi dog tre kolonner, som vi ønsker at returnere data fra, så vi har brug for en måde at let ændre på kolonne indeksnummer uden at redigere vores opslag formel - det er her KOLONNE funktion kommer ind.

Ved at indtaste det som kolonne indeksnummer argument, det vil ændre sig som opslag formel er kopieret fra celle D2 til celler E2 og F2 senere i vejledningen.

Nestende funktioner

Det KOLONNE funktion virker derfor som LOPSLAG's kolonne indeksnummer argument; dette opnås ved at neste KOLONNE funktion inde i LOPSLAG i Col_index_num linje i dialogboksen.

Indtastning af COLUMN-funktionen manuelt

Når nesting funktioner, tillader Excel ikke os at åbne den anden funktionens dialogboks for at indtaste sine argumenter. Det KOLONNE funktion skal derfor indtastes manuelt i Col_index_num linje. Det KOLONNE funktion har kun ét argument - den Reference argument, som er en cellehenvisning.

Valg af COLUMN-funktionens referenceargument

Det KOLONNE funktionens job er at returnere nummeret på den kolonne, der er angivet som Reference argument. Med andre ord konverterer kolonnebrevet til et nummer med kolonne A være den første kolonne kolonne B den anden og så videre.

Siden det første datafelt er det, vi ønsker at returnere, prisen på varen, som findes i kolonne to af datatabellen. Vi kan vælge cellehenvisningen for en celle i kolonne B som den Reference Argument for at få nummeret 2 for Col_index_num argument.

1. I LOPSLAG funktion dialogboksen, klik på knappen Col_index_num linje.
2. Indtast funktionsnavnet kolonne efterfulgt af en åben rund beslag.
3. Klik på celle B1 i regnearket for at indtaste den cellehenvisning som Reference argument.
4. Skriv a lukke runde beslag at fuldføre KOLONNE fungere
5. Forlad LOPSLAG funktionsdialogboksen åbnes til næste trin i vejledningen.

08 af 10

Indtastning af VLOOKUP Range Lookup Argument

LOPSLAG's range_lookup argument er en logisk værdi (RIGTIGT eller FALSK), der angiver, om du vil have det LOPSLAG at finde en nøjagtig eller en omtrentlig kamp til opslagsværdi.

• Hvis RIGTIGT eller hvis dette argument udelades: LOPSLAG returnerer enten et nøjagtigt match til opslagsværdiEller hvis en nøjagtig kamp ikke findes, LOPSLAG returnerer den næststørste værdi. For formlen til dette gøres dataene i den første kolonne af tabelmatrix skal sorteres i stigende rækkefølge.
• Hvis FALSK: LOPSLAG vil kun bruge et nøjagtigt match til opslagsværdi. Hvis der er to eller flere værdier i den første kolonne af tabelmatrix der matcher opslagsværdien, anvendes den første værdi, der er fundet. Hvis en eksakt match ikke findes, a # N / A fejl returneres.

I denne vejledning vil vi sætte op, da vi leder efter specifikke oplysninger om en bestemt hardwareartikel range_lookup svarende til Falsk.

1. Klik på range_lookup linje i dialogboksen.
2. Skriv ordet Falsk i denne linje for at indikere, at vi vil have LOPSLAG at returnere et nøjagtigt match for de data, vi søger.
3. Klik Okay for at fuldføre opsætningsformlen og lukke dialogboksen.
4. Da vi endnu ikke har indtastet kriterierne for opslag i celle D2 en # N / A-fejl vil være til stede i celle E2.
5. Denne fejl vil blive rettet, når vi vil tilføje opslagskriterierne i sidste trin i vejledningen.

09 af 10

Kopiering af opslagsformlen

Opslag formel er beregnet til at hente data fra flere kolonner af datatabellen på én gang. For at gøre dette skal opslagsformlen opholde sig i alle de felter, hvorfra vi ønsker information.

I denne vejledning ønsker vi at hente data fra kolonner 2, 3, og 4 af datatabellen - det er prisen, delnummeret og leverandørens navn, når vi indtaster et delvist navn som opslagsværdi.

Da dataene er lagt ud i et regelmæssigt mønster i regnearket, kan vi kopiere opsætningsformlen i celle E2 til celler F2 og G2.

Når formlen kopieres, opdaterer Excel den relative cellehenvisning i KOLONNE funktion (celle B1) for at afspejle formelens nye placering.

Ligeledes ændrer Excel ikke den absolutte cellehenvisning $ D $ 2 og den nævnte rækkevidde Bord som formlen er kopieret.

Der er mere end en måde at kopiere data i Excel, men sandsynligvis den nemmeste måde er at bruge Fyld håndtag.

1. Klik på celle E2, hvor opslagsformlen er placeret, for at gøre den til den aktive celle.
2. Placer musemarkør over sort firkant i nederste højre hjørne - markøren vil skifte til et plustegn for at angive fyldehåndtaget.
3. Klik på venstre museknap og Træk fyldehåndtaget over til celle G2.
4. Frigøre museknappen og celle F3 bør indeholde den todimensionelle opslag formel.
5. Hvis det gøres korrekt, celler F2 og G2 bør nu også indeholde # N / A fejl, der er til stede i celle E2.

10 af 10

Indtastning af opslagskriterierne

Når opsætningsformlen er blevet kopieret til de krævede celler, kan den bruges til at hente oplysninger fra datatabellen. For at gøre det skal du skrive Navn på varen du ønsker at hente ind i opslagsværdi celle (D2) og tryk på Gå ind tast på tastaturet.

Når en gang er færdig, skal hver celle, der indeholder opslaget, indeholde et andet stykke data om det hardwareelement, du søger efter.

1. Klik på celle D2 i regnearket.
2. Type Widget ind i celle D2 og tryk på Gå ind tast på tastaturet.
3. Følgende oplysninger skal vises i celler E2 til G2.
   1. E2: $ 14,76 - prisen på en widget
   2. F2: PN-98769 - delnummeret for en widget
   3. G2: Widgets Inc. - leverandørens navn til widgets
4. Test den LOPSLAG array formel yderligere ved at skrive navnet på andre dele i celle D2 og observere resultaterne i celler E2 til G2