4 Excel Lookup funktioner för att effektivt söka kalkylblad

För det mesta är det ganska lätt att hitta en cell i ett Excel-kalkylblad. Om du inte bara kan skanna igenom raderna och kolumnerna för det, kan du använda CTRL + F för att söka efter det. Men om du arbetar med ett riktigt stort kalkylblad Hur man delar ett enormt CSV Excel-kalkylblad i separata filer Hur man delar ett stort CSV Excel-kalkylblad i separata filer En av Microsofts Excel-brister är den begränsade storleken på ett kalkylblad. Om du behöver göra din Excel-fil mindre eller dela upp en stor CSV-fil, läs vidare! Läs mer, det kan spara mycket tid att använda en av dessa fyra uppslagsfunktioner. Oavsett storleken på ditt Microsoft Excel-dokument, blir de mycket effektivare.

VLOOKUP-funktionen

Med den här funktionen kan du ange en kolumn och ett värde och returnera ett värde från motsvarande rad i en annan kolumn (om det inte är meningsfullt blir det klart i ett ögonblick). Två exempel där du kan göra detta tittar upp en anställds efternamn genom deras anställningsnummer eller hitta ett telefonnummer genom att ange ett efternamn. Här är funktionens syntax:

= VLOOKUP ([lookup_value], [table_array], [col_index_num], [range_lookup])

[Lookup_value] är den information du redan har till exempel om du behöver veta vilken stat en stad är i, skulle det vara namnet på staden. [table_array] kan du ange de celler där funktionen kommer att leta efter uppslag och returvärden. När du väljer ditt intervall, se till att den första kolumnen som ingår i din array är den som innehåller ditt uppslagsvärde! Detta är avgörande. [col_index_num] är numret på kolumnen som innehåller returvärdet.

[range_lookup] är ett valfritt argument och tar 1 eller 0. Om du anger 1 eller släpper bort det här argumentet söker funktionen efter det värde du angav eller det näst lägsta numret. Så på bilden nedan kommer en VLOOKUP som letar efter ett SAT-poäng på 652 att returnera 646, eftersom det är det närmaste numret i listan som är mindre än 652 och [range_lookup] är vanligtvis 1.

Låt oss ta en titt på hur vi kan använda det här. Som jag har i mina artiklar om booleska operatörer i Microsoft Excel Mini Excel-handledning: Använd Boolean Logic för att bearbeta komplexa data Mini Excel-handledning: Använd Boolean Logic för att bearbeta komplexa data Logiska operatörer om, inte, och, och eller, kan hjälpa dig att komma ifrån Excel nybörjare till strömanvändare. Vi förklarar grunderna för varje funktion och visar hur du kan använda dem för maximala resultat. Läs mer och avancerad räkning och lägga till Mini Excel-handledning: Använda avancerad räkning och lägga till funktioner i Excel Mini Excel-handledning: Använda avancerad räkning och lägga till funktioner i Excel Att räkna och lägga till formler kan verka vardagliga jämfört med mer avancerade Excel-formler. Men de kan hjälpa dig att spara mycket tid när du behöver samla information om data i kalkylbladet. Läs mer, jag använder ett kalkylblad som jag genererade med generatedata.com. Den innehåller ID-nummer, för- och efternamn, stad, stat och SAT-poäng. Låt oss säga att jag vill hitta SAT-poäng för en person med efternamnet “Winters.” VLOOKUP gör det enkelt. Här är den formel vi skulle använda:

= VLOOKUP ("Winters", C2: F101, 4, 0)

Eftersom SAT-poängen är den fjärde kolumnen över från efternamnskolumnen har jag använt 4 för kolumnindex-argumentet. Observera att när du letar efter text är inställning av [range_lookup] till 0 en bra ide; utan det kan du få dåliga resultat. Här är vad Microsoft Excel ger oss:

Den återvände 651, SAT-poängen tillhör den student som heter Kennedy Winters, som är i rad 92 (visas i insatsen ovan). Det skulle ha gått mycket längre för att bläddra genom att leta efter namnet än det gjorde för att snabbt skriva ut syntaxen!

VLOOKUP kan också vara ganska användbart om du använder Microsoft Excel för att göra dina skatter Att göra dina skatter? 5 Excel-formulär du måste veta att göra dina skatter? 5 Excel-formulär Du måste veta Det är två dagar innan dina skatter är förfallen och du vill inte betala en annan sen anmälningsavgift. Det här är dags att utnyttja kraften i Excel för att få allt i ordning. Läs mer .

Anteckningar om VLOOKUP

Några saker är bra att komma ihåg när du använder VLOOKUP. Först och främst, som jag nämnde tidigare, se till att den första kolumnen i ditt sortiment är den som innehåller ditt uppslagsvärde. Om det inte finns i den första kolumnen kommer funktionen att returnera felaktiga resultat. Om dina kolumner är välorganiserade ska det inte vara ett problem.

Den andra sak att tänka på är att VLOOKUP bara kommer att returnera ett värde. Om vi ​​hade använt “georgien” som uppslaget värde skulle det ha returnerat poängen för den första studenten från Georgien, och gav ingen indikation på att det faktiskt finns två studenter från Georgien.

HLOOKUP-funktionen

Om VLOOKUP hittar motsvarande värden i en annan kolumn, hittar HLOOKUP motsvarande värden i en annan rad. Eftersom det oftast är enklast att skanna igenom kolumnrubriker tills du hittar rätt och använder ett filter för att hitta det du letar efter, används HLOOKUP bäst när du har verkligen stora kalkylblad eller du arbetar med värden som är organiserade efter tiden. Här är syntaxen:

= HLOOKUP ([lookup_value], [table_array], [row_index_num], [range_lookup])

[lookup_value], igen, är det värde du känner och vill hitta ett motsvarande värde för. [table_array] är cellerna där du vill söka. [row_index_num] anger den rad som returvärdet kommer ifrån. Och [range_lookup] är samma som ovan; lämna det tomt för att få närmaste värde när det är möjligt, eller skriv 0 för att bara leta efter exakta träffar.

I det här exemplet har jag skapat ett nytt kalkylblad med generatedata.com. Den här innehåller en rad för varje stat, tillsammans med ett SAT-poäng (vi säger att det är medeltalet för staten) under åren 2000-2014. Vi använder HLOOKUP för att hitta medelvärdet i Minnesota 2013. Så här gör vi det:

= HLOOKUP (2013, A1: P51, 24)

(Observera att 2013 inte finns i citat eftersom det är ett tal och inte en sträng, även de 24 kommer från Minnesota som ligger i 24: e raden.) Som du kan se i bilden nedan returneras poängen:

Minnesotans har en genomsnittlig poäng på 1014 år 2013.

Anteckningar om HLOOKUP

Som med VLOOKUP måste uppslagningsvärdet vara i den första raden i din tabellmatris; Det här är sällan ett problem med HLOOKUP, eftersom du brukar använda en kolumnrubrik för ett uppslagningsvärde. HLOOKUP returnerar bara ett enda värde.

INDEX och MATCH-funktionerna

INDEX och MATCH är två olika funktioner, men när de används tillsammans kan de göra en större snabbare sökning i ett stort kalkylblad. Båda funktionerna har nackdelar, men genom att kombinera dem bygger vi på båda sidornas styrkor. För det första, syntaxen:

= INDEX ([array], [row_number], [column_number]) = MATCH ([lookup_value], [lookup_array], [match_type])

I INDEX är [array] det array där du söker. [row_number] och [column_number] kan användas för att begränsa din sökning; vi tar en titt på det på ett ögonblick.

MATCHs [lookup_value] är ett sökord som kan vara en sträng eller ett tal; [lookup_array] är den matris där Microsoft Excel ska leta efter sökordet. [match_type] är ett valfritt argument som kan vara 1, 0 eller -1; 1 kommer att returnera det största värdet som är mindre än eller lika med din sökterm; 0 returnerar bara din exakta term; och -1 returnerar det minsta värdet som är större än eller lika med din sökterm.

Det kan inte vara klart hur vi ska använda dessa två funktioner tillsammans, så jag lägger den ut här. MATCH tar en sökterm och returnerar en cellreferens. I bilden nedan kan du se att i en sökning efter värdet 646 i kolumn F återgår MATCH 4.

INDEX, å andra sidan, gör motsatsen: det tar en cellreferens och returnerar värdet i det. Du kan se här när INDEX returneras när den beräknas återvända till kolumnen Stadens sjätte cell “Ankring,” värdet från rad 6.

Vad vi ska göra är att kombinera de två så att MATCH returnerar en cellreferens och INDEX använder den referensen för att leta upp värdet i en cell. Låt oss säga att du kom ihåg att det var en student vars efternamn var Waters, och du vill se vad den här studentens poäng var. Här är den formel vi ska använda:

= INDEX (F: F, MATCH ("Waters", C: C, 0))

Du märker att matchningstypen är satt till 0 här; när du letar efter en sträng så är det vad du vill använda. Här är vad vi får när vi kör den funktionen:

Som du kan se från insatsen, gjorde Owen Waters 1720, numret som visas när vi kör funktionen. Det här kanske inte verkar vara så användbart när du bara kan se några kolumner över men föreställa dig hur mycket tid du skulle spara om du var tvungen att göra det 50 gånger på en stor databaskalkylblad Excel Vs. Access - Kan ett kalkylblad ersätta en databas? Excel Vs. Access - Kan ett kalkylblad ersätta en databas? Vilket verktyg ska du använda för att hantera data? Access och Excel har båda data filtrering, sortering och frågande. Vi visar dig vilken som passar bäst för dina behov. Läs mer som innehöll flera hundra kolumner!

Låt sökningen börja

Microsoft Excel har mycket extremt kraftfulla funktioner 3 Crazy Excel-formulär som gör fantastiska saker 3 Crazy Excel-formulär som gör fantastiska saker Villkorliga formateringsformler i Microsoft Excel kan göra underbara saker. Här är några snygga Excel-produktivitetstryck. Läs mer, och de fyra som anges ovan klibbar bara ytan. Även med denna översiktliga översikt, bör du dock kunna spara mycket tid när du arbetar med stora kalkylblad. Om du vill se hur mycket mer du kan göra med INDEX, kolla in “Det imponerande indexet” hos Microsoft Excel Hero.

Hur kör du sökningar i stora kalkylblad? Hur har du funnit att dessa uppslagsfunktioner är till hjälp? Dela dina tankar och erfarenheter nedan!

Bildkrediter: förstoringsglas på ett kalkylblad Via Shutterstock

Utforska mer om: Microsoft Excel, kalkylblad.