VLOOKUP är en av de mest använda funktionerna i Excel. Den letar efter ett värde i ett område och returnerar ett motsvarande värde i ett angivet kolumnnummer.
Nu stötte jag på ett problem där jag var tvungen att leta upp hela raden och returnera värdena i alla kolumner från den raden (istället för att returnera ett enda värde).
Så här var vad jag var tvungen att göra. I datauppsättningen nedan hade jag försäljningsrepresentantnamn och försäljningen som de gjorde under fyra kvartal 2012. Jag hade en nedrullningsbar meny med deras namn och jag ville extrahera den maximala försäljningen för den säljaren i de fyra kvartalen.
Jag kan komma på två olika sätt att göra detta - Med INDEX eller VLOOKUP.
Leta upp hela raden / kolumnen med hjälp av INDEX -formeln
Här är formeln jag skapade för att göra detta med Index
= STOR (INDEX ($ B $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0), 1)
Hur det fungerar:
Låt oss först titta på INDEX -funktionen som är insvept inuti LARGE -funktionen.
= INDEX ($ C $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0)
Låt oss noga analysera argumenten för INDEX -funktionen:
- Array - $ B $ 4: $ F $ 1
- Radnummer - MATCH (H3, $ B $ 4: $ B $ 13,0)
- Kolumnnummer - 0
Lägg märke till att jag har använt kolumnnummer som 0.
Tricket här är att när du använder kolumnnummer som 0 returnerar det alla värden i alla kolumner. Så om jag väljer John i rullgardinsmenyn skulle indexformeln returnera alla fyra försäljningsvärden för John {91064,71690,67574,25427}.
Nu kan jag använda funktionen Stor för att extrahera det största värdet
Pro Tips - Använd kolumn-/radnummer som 0 i indexformeln för att returnera alla värden i kolumner/rader.
Leta upp hela raden / kolumnen med hjälp av VLOOKUP -formeln
Även om indexformeln är snygg, ren och robust, är VLOOKUP -sättet lite komplext. Det slutar också med att göra funktionen flyktig. Det finns dock ett fantastiskt trick som jag skulle dela med mig av i det här avsnittet. Här är formeln:
= LARGE (VLOOKUP (H3, B4: F13, ROW (INDIRECT ("2:" & COUNTA ($ B $ 4: $ F $ 4))), FALSE), 1)
Hur det fungerar
- RAD (INDIRECT (“2:” & COUNTA ($ B $ 4: $ F $ 4))) - Denna formel returnerar en array {2; 3; 4; 5}. Observera att eftersom den använder INDIRECT gör detta denna formel flyktig.
- VLOOKUP (H3, B4: F13, ROW (INDIRECT (“2:” & COUNTA ($ B $ 4: $ F $ 4))), FALSE) - Här är den bästa delen. När du sätter ihop dessa blir det VLOOKUP (H3, B4: F13, {2; 3; 4; 5}, FALSE). Lägg nu märke till att i stället för ett enda kolumnnummer har jag gett det en rad kolumnnummer. Och VLOOKUP letar lydigt upp värden i alla dessa kolumner och returnerar en array.
- Använd nu bara LARGE -funktionen för att extrahera det största värdet.
Kom ihåg att använda Ctrl + Skift + Enter för att använda denna formel.
Pro Tips - I VLOOKUP, i stället för att använda ett enda kolumnnummer, om du använder en array med kolumnnummer, kommer det att returnera en rad sökningsvärden.
