VLOOKUP er en av de mest brukte funksjonene i Excel. Den ser etter en verdi i et område og returnerer en tilsvarende verdi i et spesifisert kolonnummer.
Nå kom jeg over et problem der jeg måtte slå opp hele raden og returnere verdiene i alle kolonnene fra den raden (i stedet for å returnere en enkelt verdi).
Så her var det jeg måtte gjøre. I datasettet nedenfor hadde jeg salgsrepresentantnavn og salget de foretok i fire kvartaler i 2012. Jeg hadde en rullegardinmeny med navnene deres, og jeg ønsket å trekke ut det maksimale salget for den salgsrepresentanten i de fire kvartalene.
Jeg kan finne på to forskjellige måter å gjøre dette på - ved hjelp av INDEX eller VLOOKUP.
Slå opp hele raden / kolonnen ved hjelp av INDEX -formelen
Her er formelen jeg opprettet for å gjøre dette ved hjelp av Index
= STOR (INDEKS ($ B $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0), 1)
Hvordan det fungerer:
La oss først se på INDEX -funksjonen som er pakket inn i LARGE -funksjonen.
= INDEKS ($ C $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0)
La oss analysere argumentene til INDEX -funksjonen nøye:
- Array - $ B $ 4: $ F $ 1
- Radnummer - MATCH (H3, $ B $ 4: $ B $ 13,0)
- Kolonnenummer - 0
Legg merke til at jeg har brukt kolonnummer som 0.
Trikset her er at når du bruker kolonnummer som 0, returnerer det alle verdiene i alle kolonnene. Så hvis jeg velger John i rullegardinmenyen, ville indeksformelen returnere alle de fire salgsverdiene for John {91064,71690,67574,25427}.
Nå kan jeg bruke funksjonen Stor til å trekke ut den største verdien
Pro Tips - Bruk kolonne-/radnummer som 0 i indeksformelen for å returnere alle verdiene i kolonner/rader.
Slå opp hele raden / kolonnen ved hjelp av VLOOKUP Formula
Selv om indeksformelen er fin, ren og robust, er VLOOKUP -måten litt kompleks. Det ender også med at funksjonen blir flyktig. Imidlertid er det et fantastisk triks som jeg vil dele i denne delen. Her er formelen:
= LARGE (VLOOKUP (H3, B4: F13, ROW (INDIRECT ("2:" & COUNTA ($ B $ 4: $ F $ 4))), FALSE), 1)
Hvordan det fungerer
- RAD (INDIRECT (“2:” & COUNTA ($ B $ 4: $ F $ 4))) - Denne formelen returnerer en matrise {2; 3; 4; 5}. Vær oppmerksom på at siden den bruker INDIRECT, gjør denne formelen flyktig.
- VLOOKUP (H3, B4: F13, ROW (INDIRECT (“2:” & COUNTA ($ B $ 4: $ F $ 4))), FALSE) - Her er den beste delen. Når du setter disse sammen, blir det VLOOKUP (H3, B4: F13, {2; 3; 4; 5}, FALSE). Legg nå merke til at i stedet for et enkelt kolonnummer har jeg gitt det en rekke kolonnetall. Og VLOOKUP søker lydig opp verdier i alle disse kolonnene og returnerer en matrise.
- Nå er det bare å bruke LARGE -funksjonen for å trekke ut den største verdien.
Husk å bruke Control + Shift + Enter for å bruke denne formelen.
Pro Tips - I VLOOKUP, i stedet for å bruke et enkelt kolonnenummer, vil du returnere en rekke oppslagsverdier hvis du bruker en rekke kolonnetall.
