Ladda ner exempelarbetsboken
Denna handledning visar hur du använder Excel HLOOKUP -funktion i Excel för att leta upp ett värde.
HLOOKUP Funktionsöversikt
HLOOKUP -funktionen Hlookup står för horisontell uppslagning. Den söker efter ett värde i den översta raden i en tabell. Returnerar sedan ett värde ett visst antal rader ner från det hittade värdet. Det är samma som en vlookup, förutom att det letar upp värden horisontellt istället för vertikalt.
(Lägg märke till hur formelinmatningarna ser ut)
HLOOKUP -funktion Syntax och inmatning:
| 1 | = HLOOKUP (lookup_value, table_array, row_index_num, range_lookup) |
uppslagningsvärde - Värdet du vill söka efter.
tabell_array -Tabellen för att hämta data.
rad_index_nummer - Radnumret för att hämta data.
range_lookup -[valfritt] En boolean för att indikera exakt matchning eller ungefärlig matchning. Standard = SANT = ungefärlig matchning.
Vad är HLOOKUP -funktionen?
Som en av de äldre funktionerna i kalkylarkets värld används HLOOKUP -funktionen för att göra Horientalisk Sökningar. Den har några begränsningar som ofta övervinns med andra funktioner, till exempel INDEX/MATCH. De flesta bord är också byggda vertikalt, men det är några gånger när det är bra att söka horisontellt.
Grundläggande exempel
Låt oss titta på ett exempel på data från en betygsbok. Vi tar upp flera exempel för att extrahera information för specifika studenter.
Om vi vill hitta vilken klass Bob är i, skulle vi skriva formeln:
| 1 | = HLOOKUP ("Bob", A1: E3, 2, FALSE) |
Viktiga saker att komma ihåg är att objektet vi söker efter (Bob) måste vara på första raden i vårt sökområde (A1: E3). Vi har berättat för funktionen att vi vill returnera ett värde från 2: annd rad i sökområdet, som i detta fall är rad 2. Slutligen angav vi att vi vill göra en exakt matchning genom att placera False som det sista argumentet. Här blir svaret "Läsning".
Sidotips: Du kan också använda talet 0 istället för False som det sista argumentet, eftersom de har samma värde. Vissa människor föredrar detta eftersom det är snabbare att skriva. Vet bara att båda är acceptabla.
Skiftad data
För att lägga till lite förtydligande i vårt första exempel behöver sökobjektet inte finnas i rad 1 i kalkylarket, bara den första raden i ditt sökintervall. Låt oss använda samma datamängd:
Låt oss nu hitta betyget för klassen Science. Vår formel skulle vara
| 1 | = HLOOKUP ("Science", A2: E3, 2, FALSE) |
Detta är fortfarande en giltig formel, eftersom den första raden i vårt sökintervall är rad 2, det är där vår sökterm "Science" finns. Vi returnerar ett värde från 2: annd rad i sökområdet, som i detta fall är rad 3. Svaret är då ”A-“.
Användning av jokertecken
HLOOKUP -funktionen stöder användningen av jokertecken “*” och “?” när man gör sökningar. Låt oss till exempel säga att vi hade glömt hur man stavar Franks namn och bara ville söka efter ett namn som börjar med "F". Vi skulle kunna skriva formeln
| 1 | = HLOOKUP ("F*", A1: E3, 2, FALSE) |
Detta skulle kunna hitta namnet Frank i kolumn E och sedan returnera värdet från 2nd relativ rad. I det här fallet blir svaret ”Vetenskap”.
Ej exakt matchning
För det mesta vill du se till att det sista argumentet i HLOOKUP är falskt (eller 0) så att du får en exakt matchning. Det finns dock några gånger när du kan leta efter en icke-exakt matchning. Om du har en lista med sorterade data kan du också använda HLOOKUP för att returnera resultatet för objektet som är antingen samma eller näst minsta. Detta används ofta när man hanterar ökande antal intervall, till exempel i en skattetabell eller provisionsbonus.
Låt oss säga att du vill hitta skattesatsen för en inkomst som anges i cell H2. Formeln i H4 kan vara:
| 1 | = HLOOKUP (H2, B1: F2, 2, TRUE) |
Skillnaden i denna formel är att vårt sista argument är "True". I vårt specifika exempel kan vi se att när våra individer lägger in en inkomst på 45 000 dollar kommer de att ha en skattesats på 15%.
Notera: Även om vi vanligtvis vill ha en exakt matchning med False som argument, glömmer du att ange 4: anth -argumentet i en HLOOKUP är standarden True. Detta kan göra att du får oväntade resultat, särskilt när du hanterar textvärden.
Dynamisk rad
HLOOKUP kräver att du anger ett argument som säger vilken rad du vill returnera ett värde från, men tillfället kan uppstå när du inte vet var raden kommer att vara, eller om du vill tillåta din användare att ändra vilken rad att återvända från. I dessa fall kan det vara bra att använda MATCH -funktionen för att bestämma radnumret.
Låt oss överväga vårt betygsboksexempel igen, med några ingångar i G2 och G4. För att få kolumnnumret kan vi skriva en formel för
| 1 | = MATCH (G2, A1: A3, 0) |
Detta kommer att försöka hitta den exakta positionen för "Grade" inom intervallet A1: A3. Svaret blir 3. Genom att veta detta kan vi ansluta det till en HLOOKUP -funktion och skriva en formel i G6 så här:
| 1 | = HLOOKUP (G4, A1: E3, MATCH (G2, A1: A3, 0), 0) |
Så, MATCH -funktionen kommer att utvärderas till 3, och det säger HLOOKUP att returnera ett resultat från 3rd rad i A1: E3 -serien. Sammantaget får vi sedan vårt önskade resultat av “C”. Vår formel är dynamisk nu genom att vi kan ändra antingen raden att titta på eller namnet att söka efter.
HLOOKUP begränsningar
Som nämnts i början av artikeln är det största fallet med HLOOKUP att det kräver att sökordet finns i den vänstra kolumnen i sökområdet. Även om det finns några fina knep du kan göra för att övervinna detta, är det vanliga alternativet att använda INDEX och MATCH. Den kombinationen ger dig mer flexibilitet, och det kan ibland till och med vara en snabbare beräkning.
HLOOKUP i Google Kalkylark
HLOOKUP -funktionen fungerar exakt samma i Google Kalkylark som i Excel:
Ytterligare anmärkningar
Använd HLOOKUP -funktionen för att utföra en horisontell sökning. Om du redan är bekant med VLOOKUP -funktionen fungerar en HLOOKUP på exakt samma sätt förutom att sökningen utförs horisontellt istället för vertikalt. HLOOKUP söker efter en exakt matchning (range_lookup = FALSE) eller den närmaste matchningen som är lika med eller mindre än lookup_value (range_lookup = SANT, endast numeriska värden) på den första raden i tabellen_array. Det returnerar sedan ett motsvarande värde, n antal rader under matchningen.
När du använder en HLOOKUP för att hitta en exakt matchning definierar du först ett identifieringsvärde som du vill söka efter som uppslagningsvärde. Detta identifieringsvärde kan vara ett SSN, medarbetar -ID, namn eller någon annan unik identifierare.
Därefter definierar du intervallet (kallas tabell_array) som innehåller identifierarna i den översta raden och de värden som du slutligen vill söka efter i raderna under den. VIKTIGT: De unika identifierarna måste finnas på den översta raden. Om de inte är det måste du antingen flytta raden till toppen eller använda MATCH / INDEX istället för HLOOKUP.
Tredje, definiera radnummer (row_index) av tabell_array som du vill återvända. Tänk på att den första raden, som innehåller de unika identifierarna, är rad 1. Den andra raden är rad 2, etc.
Sist måste du ange om du vill söka efter en exakt matchning (FALSE) eller närmaste match (SANN) i range_lookup. Om alternativet för exakt matchning är valt och en exakt matchning inte hittas, returneras ett fel (#N/A). För att få formeln att returnera tom eller "inte hittad", eller något annat värde i stället för felvärdet (#N/A), använd IFERROR -funktionen med HLOOKUP.
För att använda HLOOKUP -funktionen för att returnera en ungefärlig matchningsuppsättning: range_lookup = SANT. Det här alternativet är endast tillgängligt för numeriska värden. Värdena måste sorteras i stigande ordning.
HLOOKUP Exempel i VBA
Du kan också använda HLOOKUP -funktionen i VBA. Typ:application.worksheetfunction.hlookup (lookup_value, table_array, row_index_num, range_lookup)
Utför följande VBA -uttalanden
| 123456 | Range ("G2") = Application.WorksheetFunction.HLookup (Range ("C1"), Range ("A1: E3"), 1)Range ("H2") = Application.WorksheetFunction.HLookup (Range ("C1"), Range ("A1: E3"), 2)Range ("I2") = Application.WorksheetFunction.HLookup (Range ("C1"), Range ("A1: E3"), 3)Range ("G3") = Application.WorksheetFunction.HLookup (Range ("D1"), Range ("A1: E3"), 1)Range ("H3") = Application.WorksheetFunction.HLookup (Range ("D1"), Range ("A1: E3"), 2)Range ("I3") = Application.WorksheetFunction.HLookup (Range ("D1"), Range ("A1: E3"), 3) |
kommer att ge följande resultat
För funktionsargumenten (lookup_value, etc.) kan du antingen ange dem direkt i funktionen eller definiera variabler som ska användas istället.
Återgå till listan över alla funktioner i Excel
