I VLOOKUP -funktionen definierar vi ofta col_index_no static. Vi koderar det inom VLOOKUP -formeln, som VLOOKUP (id, data,3, 0). Problemet uppstår när vi sätter in eller tar bort en kolumn i data. Om vi tar bort eller lägger till en kolumn före eller efter den tredje kolumnen kommer den tredje kolumnen inte längre att referera till den avsedda kolumnen. Detta är ett problem. Annat är när du har flera kolumner att leta upp. Du måste redigera kolumnindexet i varje formel. Enkel kopiering klistras inte in.
Men vad sägs om du kan berätta VLOOKUP att titta på rubriker till och endast returnera matchande rubriker. Detta kallas tvåvägs VLOOKUP.
Till exempel, om jag har en VLOOKUP -formel förmärken kolumn, då ska VLOOKUP leta efter märken kolumn i data och returvärde från den kolumnen. Detta kommer att lösa vårt problem.
Hmm … Okej, så hur gör vi det? Genom att använda matchningsfunktionen inom VLOOKUP -funktionen.
Generisk formel
=VLOOKUP(lookup_value, table_array, MATCH (lookup_heading, table_headings, 0), 0)
Lookup_value: uppslagsvärdet i den första kolumnen i table_array.
Tabell_array: intervallet där du vill leta. Exempelvis A2, D10.
Lookup_heading: rubriken du vill leta upp i tabell_arrays rubriker.
Tabellrubriker: Referens till rubrikerna i tabellmatrisen. T.ex. om tabellen är A2, D10 och rubriker högst upp i varje kolumn, då dess A1: D1.
Så nu vet vi vad vi behöver för dynamisk col_index, låt oss göra allt klart med ett exempel.
Dynamiskt VLOOKUP Exempel
För det här exemplet har vi den här tabellen som innehåller data från elever i intervallet A4: E16.
Med rullning nr och rubrik vill jag hämta data från den här tabellen. I det här fallet, i cell H4, vill jag få data från rulle nr skriven i cell G4 och rubrik i H3. Om jag ändrar rubriken ska data från respektive område hämtas i cell H4.
Skriv denna formel i cell H4
= VLOOKUP (G4, B4: E16, MATCH (H3, B3: E3,0), 0)
Eftersom vår tabellmatris är B4: E16 blir vår rubrikmatris B3: E3.
Notera: Om dina data är välstrukturerade kommer kolumnrubrikerna att ha samma antal kolumner, och det är den första raden i tabellen.
Hur det fungerar:
Så huvudsakligen utvärderar kolumnindexnumret automatiskt. För att göra det använde vi funktionen MATCH.
MATCH (H3, B3: E3,0): Eftersom H3 innehåller "student" kommer MATCH att returnera 2. Om H3 hade "Betyg" det, skulle det ha returnerat 4 osv. VLOOKUP -formeln kommer äntligen att ha col_index_num.
= VISNING (G4, B4: E16,2,0)
Som vi vet returnerar MATCH-funktionen indexnumret för ett givet värde i det medföljande endimensionella intervallet. Därför kommer MATCH att leta upp alla värden som skrivs i H3 i intervall B3: E3 och returnerar dess indexnummer.
Nu när du ändrar rubrik i H3, om den finns i rubriker, kommer denna formel att returnera ett värde från respektive kolumn. Annars får du ett #N/A -fel.
VLOOKUP i flera kolumner snabbt
I exemplet ovan behövde vi svaret från ett kolumnvärde. Men vad händer om du vill få flera kolumner samtidigt. Om du kopierar ovanstående formel returnerar det fel. Vi måste göra några mindre ändringar i den för att göra den bärbar.
Använda absoluta referenser med VLOOKUP
Skriv nedanstående formel i Cell H2.
= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, MATCH (H $ 1, $ B $ 1: $ E $ 1,0), 0)
Kopiera nu H2 i alla celler i intervall H2: J6 för att fylla den med data.
Hur det fungerar:
Här har jag gett absolut referens av varje intervall utom rad i uppslagsvärde för VLOOKUP ($ G2) och kolumn i lookup_value för MATCH (H $ 1).
$ G2: Detta gör att raden kan ändras för uppslagsvärde för VLOOKUP -funktionen medan den kopieras nedåt men begränsar kolumnen för att ändras när den kopieras till höger. Vilket får VLOOKUP att leta efter Id från G -kolumnen bara med den relativa raden.
Liknande, H $ 1 gör att kolumnen kan ändras när den kopieras horisontellt och begränsar raden när den kopieras nedåt.
Använda namngivna områden
Ovanstående exempel fungerar bra men blir utmanande att läsa och skriva denna formel. Och det här är inte alls bärbart. Detta kan förenklas med namngivna intervall.
Vi kommer att göra lite namn här först. För det här exemplet namngav jag
$ B $ 2: $ E $ 14: som data
$ B $ 1: $ E $ 1: som rubriker
H $ 1: Namnge det som rubrik. Gör kolumnerna relativa. Välj H1 för att göra det. Tryck på CTRL+F3, klicka på nytt, i avsnittet Avser "ta bort" från framsidan av H.
$ G2: På samma sätt, namnge det som RollNo. Den här gången gör raden relativ genom att ta bort '$' från framsidan av 2.
Nu, när du har alla namn på arket, skriver du den här formeln var som helst i excel -filen. Det kommer alltid att få rätt svar.
= VLOOKUP (RollNo, Data, MATCH (Rubrik, Rubriker, 0), 0)
Se, vem som helst kan läsa detta och förstå det.
Så med hjälp av dessa metoder kan du göra col_index_num dynamisk. Låt mig veta om detta var till hjälp i kommentarfältet nedan.
Hur man använder than VLOOKUP -funktion i Excel
Relativ och absolut referens i Excel
Namngivna intervall i Excel
Hur man VLOOKUP från olika Excel -blad
VLOOKUP Flera värden
Populära artiklar
50 Excel -genväg för att öka din produktivitet : Bli snabbare på din uppgift. Dessa 50 genvägar får dig att arbeta ännu snabbare med Excel.
Hur man använder than VLOOKUP -funktion i Excel : Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.
Så här använder du funktionen COUNTIF i Excel : Räkna värden med villkor med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifika värden. Countif -funktionen är avgörande för att förbereda din instrumentpanel.
Hur man använder SUMIF -funktionen i Excel : Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.