Hvis du har læst Excel-tip i et stykke tid, har du altid fundet nogen, der taler om at bruge Excel INDEX () & MATCH () -funktioner i stedet for Excel VLOOKUP. Når jeg talte for mig selv, var det altid for svært at prøve at mestre TO nye funktioner samtidigt. Men det er et sejt trick. Giv mig fem minutter, så prøver jeg at forklare det på enkel engelsk.
Den 30 sekunders gennemgang af VLOOKUP
Sig, at du har en tabel over medarbejderregistreringer. Den første kolonne er et medarbejdernummer, og de resterende kolonner er forskellige data om medarbejderen. Hver gang du har et medarbejdernummer i regnearket, kan du bruge VLOOKUP til at returnere et specifikt nulpunkt om medarbejderen. Syntaksen er VLOOKUP (værdi, dataområde, kolonnr., FALSK). Det siger til Excel: "Gå til dataområdet. Find en række, der har (værdi) i den første kolonne i dataområdet. Returner (kol. Nr.) Th-værdien fra den række. Når du har fået fat i det, det er meget simpelt og kraftfuldt.
Problemet
En dag har du en situation, hvor du har medarbejdernavnet, men har brug for medarbejdernummeret. I det følgende billede har du et navn i A10 og har brug for at finde medarbejdernummeret i B10.
Når nøglefeltet er til højre for de data, du vil hente, fungerer VLOOKUP ikke. Hvis kun VLOOKUP accepterer -1 som kolonnenummer, ville der ikke være noget problem. Men det gør det ikke. En almindelig løsning er midlertidigt at indsætte en ny kolonne A, kopiere kolonnen med navne til den nye kolonne A, udfylde med VLOOKUP, indsætte specielle værdier og derefter slette den midlertidige kolonne A. Excel-professionelle kan sandsynligvis gøre dette skridt i deres søvn.
Jeg vil foreslå, at du tager udfordringen og prøver at bruge denne metode med et enkelt trin. Ja, du bliver nødt til at lægge formlen op på din væg i et par uger, men det gjorde du også med VLOOKUP for længe siden, ikke?
Jeg tror, at årsagen til, at dette er så svært, er, at du bruger to funktioner, som du sandsynligvis aldrig har brugt før. Så lad mig opdele det i to stykker.
For det første er der INDEX () -funktionen. Dette er en forfærdeligt navngivet funktion. Når nogen siger "indeks", fremmaner det ikke noget i mit sind, der ligner det, denne funktion gør. Indeks kræver tre argumenter.
=INDEX(data range, row number, column number)
På engelsk går Excel til dataområdet og returnerer værdien i skæringspunktet mellem (række nummer) og række (kolonne nummer) kolonne. Hej, tænk over det - dette er ret simpelt, ikke? =INDEX($A$2:$C$6,4,2)giver dig værdien i B5.
Hvis du anvender INDEX () på vores problem, kan du regne med, at du vil bruge dette for at returnere medarbejdernummeret fra området =INDEX($A$2:$A$6,?,1). Faktisk virker dette stykke af det så trivielt, at det virker ubrugeligt. Men når du erstatter spørgsmålstegnet med en MATCH () -funktion, har du løsningen.
Her er syntaksen:
=MATCH(Value, Single-column data range, FALSE)
Det fortæller Excel, "Søg i dataområdet og fortæl mig det relative række nummer, hvor du finder et match for (data). Så for at finde hvilken række, der har medarbejderen i A10, skal du bruge =MATCH(A10,$B$2:$B$6,FALSE). Ja, dette er mere komplekst end Index , men det skulle være lige op ad VLOOKUP-proffernes gyde. Hvis A10 indeholder "Miller, Bob", vender denne MATCH tilbage, at han er i 3. række i området B2: B6.
Der er det - MATCH () -funktionen fortæller indeksfunktionen, hvilken række man skal se i - du er færdig. Tag indeksfunktionen, udskift vores spørgsmålstegn med MATCH-funktionen, og du kan nu gøre det svarende til VLOOKUPs, når nøglefeltet ikke er i venstre kolonne. Her er funktionen, der skal bruges:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Sticky notes på min væg viser det faktisk som to linjer. Først skrev jeg forklaringen på MATCH () ud. Nedenfor skrev jeg forklaringen på INDEX (). Jeg tegnede derefter en tragtform mellem de to for at indikere, at MATCH () -funktionen falder ind i det 2. argument for INDEX () -funktionen.
De første par gange, jeg var nødt til at gøre en af disse, blev jeg fristet til bare at smække en ny midlertidig kolonne A derinde, men gik igennem smerten ved at gøre det på denne måde i stedet. Det er hurtigere og kræver mindre manipulation. Så næste gang du ønsker, at du kan sætte et negativt tal i VLOOKUP-funktionen, skal du prøve denne mærkelige kombination af INDEX og MATCH for at løse dine problemer.
