Hvis du har lest Excel-tips en stund, har du alltid funnet noen som snakker om å bruke Excel INDEX () & MATCH () -funksjoner i stedet for Excel VLOOKUP. Når jeg snakket for meg selv, var det alltid for vanskelig å prøve å mestre TO nye funksjoner samtidig. Men det er et kult triks. Gi meg fem minutter, så skal jeg prøve å forklare det på enkel engelsk.
Den 30 sekunders gjennomgangen av VLOOKUP
Si at du har en tabell over ansattes poster. Den første kolonnen er et ansattnummer, og de resterende kolonnene er forskjellige data om den ansatte. Hver gang du har et ansattnummer i regnearket, kan du bruke VLOOKUP for å returnere et spesifikt datum om den ansatte. Syntaksen er VLOOKUP (verdi, dataområde, kolonnr., FALSE). Det sier til Excel: "Gå til dataområdet. Finn en rad som har (verdi) i den første kolonnen i dataområdet. Returner (kol. Nr.) -Verdien fra den raden. Når du har fått tak i det, det er veldig enkelt og kraftig.
Problemet
En dag har du en situasjon der du har ansattes navn, men trenger ansattnummer. I det følgende bildet har du et navn i A10 og trenger å finne ansattnummeret i B10.
Når nøkkelfeltet er til høyre for dataene du vil hente, vil ikke VLOOKUP ikke fungere. Hvis bare VLOOKUP ville godta -1 som kolonnenummer, ville det ikke være noe problem. Men det gjør det ikke. En vanlig løsning er å midlertidig sette inn en ny kolonne A, kopiere kolonnen med navn til den nye kolonnen A, fylle ut med VLOOKUP, Lim inn spesielle verdier, og deretter slette den midlertidige kolonnen A. Excel-proffer kan trolig gjøre dette i søvn.
Jeg skal foreslå at du tar utfordringen og prøver å bruke denne enkle trinnsmetoden. Ja, du må ta formelen opp på veggen din i noen uker, men du gjorde det med VLOOKUP for lenge siden, ikke sant?
Jeg tror grunnen til at dette er så vanskelig er at du bruker to funksjoner som du sannsynligvis aldri har brukt før. Så la meg dele den opp i to stykker.
Først er det INDEX () -funksjonen. Dette er en fryktelig navngitt funksjon. Når noen sier "indeks", tryller det ikke frem noe i mitt sinn som ligner på hva denne funksjonen gjør. Indeks krever tre argumenter.
=INDEX(data range, row number, column number)
På engelsk går Excel til dataområdet og returnerer verdien i skjæringspunktet mellom (radnummer) og rad (kolonne nummer) kolonne. Hei, tenk på det - dette er ganske enkelt, ikke sant? =INDEX($A$2:$C$6,4,2)vil gi deg verdien i B5.
Bruk av INDEX () til vårt problem, kan du finne at å returnere ansattnummer fra området, vil du bruke denne: =INDEX($A$2:$A$6,?,1). Egentlig virker dette stykke av det så trivielt at det virker ubrukelig. Men når du erstatter spørsmålstegnet med en MATCH () -funksjon, har du løsningen.
Her er syntaksen:
=MATCH(Value, Single-column data range, FALSE)
Det forteller Excel: "Søk i dataområdet og fortell meg det relative radnummeret der du finner en kamp for (data). Så hvis du vil finne hvilken rad som har den ansatte i A10, vil du bruke =MATCH(A10,$B$2:$B$6,FALSE). Ja, dette er mer komplisert enn Index , men det burde være rett opp i smuget til VLOOKUP-proffene. Hvis A10 inneholder "Miller, Bob", vil denne MATCHEN returnere at han er i 3. rad i området B2: B6.
Der er det - MATCH () -funksjonen forteller indeksfunksjonen hvilken rad du skal se i - du er ferdig. Ta indeksfunksjonen, erstatt spørsmålstegnet vårt med MATCH-funksjonen, og du kan nå gjøre tilsvarende VLOOKUPs når nøkkelfeltet ikke er i venstre kolonne. Her er funksjonen du skal bruke:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Klistrelappen på veggen min viser den faktisk som to linjer. Først skrev jeg ut forklaringen på MATCH (). Under det skrev jeg forklaringen på INDEX (). Jeg tegnet en traktform mellom de to for å indikere at MATCH () -funksjonen faller inn til det andre argumentet til INDEX () -funksjonen.
De første gangene jeg måtte gjøre en av disse, ble jeg fristet til å bare smelle en ny midlertidig kolonne A der inne, men gikk gjennom smerten ved å gjøre det på denne måten i stedet. Det er raskere, og krever mindre manipulasjon. Så neste gang du ønsker at du kan sette et negativt tall i VLOOKUP-funksjonen, kan du prøve denne rare kombinasjonen av INDEX og MATCH for å løse problemene dine.
