VERT.ZOEKEN is een van de meest gebruikte functies in Excel. Het zoekt naar een waarde in een bereik en retourneert een overeenkomstige waarde in een opgegeven kolomnummer.
Nu kwam ik een probleem tegen waarbij ik de hele rij moest opzoeken en de waarden in alle kolommen van die rij moest retourneren (in plaats van een enkele waarde terug te geven).
Dus hier is wat ik moest doen. In de onderstaande dataset had ik de namen van verkopers en de verkopen die ze in 2012 in 4 kwartalen hebben gedaan. Ik had een vervolgkeuzelijst met hun namen en ik wilde de maximale verkoop voor die vertegenwoordiger in die vier kwartalen extraheren.
Ik zou 2 verschillende manieren kunnen bedenken om dit te doen - INDEX of VERT.ZOEKEN gebruiken.
Gehele rij/kolom opzoeken met INDEX-formule
Hier is de formule die ik heb gemaakt om dit te doen met behulp van Index
=GROOT(INDEX($B$4:$F$13,MATCH(H3,$B$4:$B$13,0),0),1)
Hoe het werkt:
Laten we eerst kijken naar de INDEX-functie die in de LARGE-functie is verpakt.
=INDEX($C$4:$F$13,MATCH(H3,$B$4:$B$13,0),0)
Laten we de argumenten van de INDEX-functie nauwkeurig analyseren:
- Array - $B$4:$F$1
- Rijnummer - MATCH(H3,$B$4:$B$13,0)
- Kolomnummer - 0
Merk op dat ik kolomnummer als 0 heb gebruikt.
De truc hier is dat wanneer u kolomnummer als 0 gebruikt, het alle waarden in alle kolommen retourneert. Dus als ik John selecteer in de vervolgkeuzelijst, zou de indexformule alle 4 verkoopwaarden voor John retourneren {91064.71690.67574.25427}.
Nu kan ik de functie Groot gebruiken om de grootste waarde te extraheren
Pro-tip - Gebruik het kolom-/rijnummer als 0 in de indexformule om alle waarden in kolommen/rijen te retourneren.
Gehele rij/kolom opzoeken met de formule VERT.ZOEKEN
Hoewel de Index-formule netjes, schoon en robuust is, is de VERT.ZOEKEN-manier een beetje ingewikkeld. Het maakt de functie ook vluchtig. Er is echter een geweldige truc die ik in deze sectie wil delen. Hier is de formule:
=GROOT(VERT.ZOEKEN(H3,B4:F13, RIJ(INDIRECT("2:"&COUNTA($B$4:$F$4))), ONWAAR),1)
Hoe het werkt
- ROW(INDIRECT(“2:”&COUNTA($B$4:$F$4))) - Deze formule retourneert een matrix {2;3;4;5}. Merk op dat aangezien het INDIRECT gebruikt, dit deze formule vluchtig maakt.
- VERT.ZOEKEN(H3,B4:F13,ROW(INDIRECT(“2:”&COUNTA($B$4:$F$4))),FALSE) - Dit is het beste deel. Als je deze samenvoegt, wordt het VLOOKUP(H3,B4:F13,{2;3;4;5},FALSE). Merk nu op dat in plaats van een enkel kolomnummer, ik het een reeks kolomnummers heb gegeven. En VERT.ZOEKEN zoekt gehoorzaam waarden op in al deze kolommen en retourneert een array.
- Gebruik nu de LARGE-functie om de grootste waarde te extraheren.
Vergeet niet om Control + Shift + Enter te gebruiken om deze formule te gebruiken.
Pro Tip - In VERT.ZOEKEN, in plaats van een enkel kolomnummer te gebruiken, als u een array van kolomnummers gebruikt, zal het een array van opzoekwaarden retourneren.
