Om du har läst Excel-tips ett tag har du alltid hittat någon som pratar om att använda Excel INDEX () & MATCH () -funktioner istället för Excel VLOOKUP. När jag talade för mig själv var det alltid för svårt att försöka behärska två nya funktioner samtidigt. Men det är ett coolt trick. Ge mig fem minuter så ska jag försöka förklara det på enkel engelska.
Den 30 andra granskningen av VLOOKUP
Anta att du har en tabell över medarbetarregister. Den första kolumnen är ett anställningsnummer och de återstående kolumnerna är olika data om den anställde. Varje gång du har ett anställningsnummer i kalkylbladet kan du använda VLOOKUP för att returnera ett specifikt datum om den anställde. Syntaxen är VLOOKUP (värde, dataområde, kolnr, FALSK). Det säger till Excel, "Gå till dataområdet. Hitta en rad som har (värde) i den första kolumnen i dataområdet. Returnera (kol. Nr.) Värdet från den raden. När du förstår det, det är väldigt enkelt och kraftfullt.
Problemet
En dag har du en situation där du har anställds namn, men behöver anställdsnummer. I följande bild har du ett namn i A10 och måste hitta anställd nummer i B10.
När nyckelfältet är till höger om de data du vill hämta fungerar inte VLOOKUP. Om bara VLOOKUP accepterar -1 som kolumnnummer skulle det inte finnas något problem. Men det gör det inte. En vanlig lösning är att tillfälligt infoga en ny kolumn A, kopiera namnkolumnen till den nya kolumnen A, fylla i VLOOKUP, Klistra in speciella värden och sedan ta bort den tillfälliga kolumnen A. Excel-proffs kan antagligen göra detta i sömnen.
Jag kommer att föreslå att du tar utmaningen och försöker använda den här enkla stegmetoden. Ja, du kommer att behöva ta formeln upp på väggen i några veckor, men det gjorde du med VLOOKUP för länge sedan, eller hur?
Jag tror att anledningen till att detta är så svårt är att du använder två funktioner som du förmodligen aldrig använt förut. Så, låt mig dela upp den i två delar.
Först finns funktionen INDEX (). Detta är en fruktansvärt namngiven funktion. När någon säger "index", framkallar det inte något i mitt sinne som liknar vad den här funktionen gör. Index kräver tre argument.
=INDEX(data range, row number, column number)
På engelska går Excel till dataområdet och returnerar värdet i skärningspunkten mellan (radnummer) och rad (kolumnnummer) kolumn. Hej, tänk på det - det här är ganska enkelt, eller hur? =INDEX($A$2:$C$6,4,2)ger dig värdet i B5.
Tillämpa INDEX () till vårt problem, kan du räkna att gå tillbaka den anställde nummer från intervallet, skulle du använda detta: =INDEX($A$2:$A$6,?,1). Egentligen verkar denna bit av den så trivial att den verkar värdelös. Men när du ersätter frågetecknet med en MATCH () -funktion har du lösningen.
Här är syntaxen:
=MATCH(Value, Single-column data range, FALSE)
Det säger till Excel, "Sök i dataområdet och berätta det relativa radnumret där du hittar en matchning för (data). Så för att hitta vilken rad som har den anställde i A10, skulle du använda =MATCH(A10,$B$2:$B$6,FALSE). Ja, det här är mer komplicerat än Index , men det borde vara precis uppe i gränden för VLOOKUP-proffsen. Om A10 innehåller "Miller, Bob" kommer denna MATCH att återvända till att han befinner sig i 3: e raden i intervallet B2: B6.
Där är det - MATCH () -funktionen säger till indexfunktionen vilken rad du ska titta i - du är klar. Ta indexfunktionen, ersätt vårt frågetecken med MATCH-funktionen, och du kan nu göra motsvarande VLOOKUPs när nyckelfältet inte finns i den vänstra kolumnen. Här är funktionen att använda:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Klisterlappen på min vägg visar den faktiskt som två rader. Först skrev jag ut förklaringen för MATCH (). Nedan skrev jag förklaringen till INDEX (). Jag ritade sedan en trattform mellan de två för att indikera att MATCH () -funktionen faller in i det andra argumentet för INDEX () -funktionen.
De första gångerna jag var tvungen att göra en av dessa, frestades jag bara att smälla en ny tillfällig kolumn A där inne, men gick igenom smärtan att göra det på det här sättet istället. Det är snabbare och kräver mindre manipulation. Så nästa gång du önskar att du kan sätta ett negativt tal i VLOOKUP-funktionen, prova denna konstiga kombination av INDEX och MATCH för att lösa dina problem.
