VLOOKUP er en af de mest anvendte funktioner i Excel. Den leder efter en værdi i et område og returnerer en tilsvarende værdi i et specifikt kolonnenummer.
Nu stødte jeg på et problem, hvor jeg skulle slå hele rækken op og returnere værdierne i alle kolonner fra den række (i stedet for at returnere en enkelt værdi).
Så her var hvad jeg skulle gøre. I nedenstående datasæt havde jeg salgsrepræsentantnavne og salget, de foretog i 4 kvartaler i 2012. Jeg havde et drop -down med deres navne, og jeg ville udtrække det maksimale salg for den pågældende salgsrepræsentant i de fire kvartaler.
Jeg kunne finde på 2 forskellige måder at gøre dette på - Ved hjælp af INDEX eller VLOOKUP.
Opslag hele rækken / kolonnen ved hjælp af INDEX -formel
Her er formlen, jeg oprettede for at gøre dette ved hjælp af Index
= STOR (INDEX ($ B $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0), 1)
Hvordan det virker:
Lad os først se på INDEX -funktionen, der er pakket ind i LARGE -funktionen.
= INDEX ($ C $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0)
Lad os nøje analysere argumenterne for INDEX -funktionen:
- Array - $ B $ 4: $ F $ 1
- Rækketal - MATCH (H3, $ B $ 4: $ B $ 13,0)
- Kolonnenummer - 0
Bemærk, at jeg har brugt kolonnenummer som 0.
Tricket her er, at når du bruger kolonnenummer som 0, returnerer det alle værdierne i alle kolonnerne. Så hvis jeg vælger John i rullemenuen, ville indeksformlen returnere alle de 4 salgsværdier for John {91064,71690,67574,25427}.
Nu kan jeg bruge funktionen Stor til at udtrække den største værdi
Pro Tip - Brug kolonne-/rækkenummer som 0 i indeksformlen til at returnere alle værdierne i kolonner/rækker.
Opslag hele rækken / kolonnen ved hjælp af VLOOKUP -formel
Selvom indeksformlen er pæn, ren og robust, er VLOOKUP -måde lidt kompleks. Det ender også med at gøre funktionen flygtig. Der er dog et fantastisk trick, som jeg vil dele i dette afsnit. Her er formlen:
= STOR (VLOOKUP (H3, B4: F13, ROW (INDIRECT ("2:" & COUNTA ($ B $ 4: $ F $ 4))), FALSK), 1)
Hvordan det virker
- RÆKKE (INDIRECT (“2:” & COUNTA ($ B $ 4: $ F $ 4))) - Denne formel returnerer en matrix {2; 3; 4; 5}. Bemærk, at da den bruger INDIRECT, gør dette denne formel flygtig.
- VLOOKUP (H3, B4: F13, ROW (INDIRECT (“2:” & COUNTA ($ B $ 4: $ F $ 4))), FALSK) - Her er den bedste del. Når du sætter disse sammen, bliver det VLOOKUP (H3, B4: F13, {2; 3; 4; 5}, FALSK). Bemærk nu, at i stedet for et enkelt kolonnenummer har jeg givet det en række kolonnetal. Og VLOOKUP søger lydigt op i værdier i alle disse kolonner og returnerer et array.
- Brug nu bare LARGE -funktionen til at udtrække den største værdi.
Husk at bruge Control + Shift + Enter for at bruge denne formel.
Pro tip - I VLOOKUP vil det i stedet for at bruge et enkelt kolonnenummer, returnere et array med opslagsværdier, hvis du bruger en række kolonnetal.
