Als u al een tijdje Excel-tips leest, heeft u steevast iemand gevonden die het heeft over het gebruik van Excel INDEX () & MATCH () -functies in plaats van Excel VERT.ZOEKEN. Voor mijzelf gesproken, het was altijd te moeilijk om te proberen TWEE nieuwe functies tegelijkertijd onder de knie te krijgen. Maar het IS een coole truc. Geef me vijf minuten en ik zal proberen het in eenvoudig Engels uit te leggen.
De 30 seconden review van VERT.ZOEKEN
Stel dat u een tabel met werknemersrecords heeft. De eerste kolom is een personeelsnummer en de overige kolommen zijn verschillende gegevens over de werknemer. Elke keer dat u een werknemersnummer in het werkblad heeft, kunt u VERT.ZOEKEN gebruiken om een specifieke datum over de werknemer te retourneren. De syntaxis is VERT.ZOEKEN (waarde, gegevensbereik, kolomnr., FALSE). Er staat tegen Excel: "Ga naar het gegevensbereik. Zoek een rij met (waarde) in de eerste kolom van het gegevensbereik. Retourneer de (kol. Nr.) E waarde uit die rij. Zodra u het onder de knie hebt, het is heel eenvoudig en krachtig.
Het probleem
Op een dag heb je een situatie waarin je de naam van de werknemer hebt, maar het werknemersnummer nodig hebt. In de volgende afbeelding hebt u een naam in A10 en moet u het personeelsnummer in B10 vinden.
Als het sleutelveld zich rechts van de gegevens bevindt die u wilt ophalen, werkt VERT.ZOEKEN niet. Als alleen VERT.ZOEKEN -1 als het kolomnummer zou accepteren, zou er geen probleem zijn. Maar dat doet het niet. Een veel voorkomende oplossing is om tijdelijk een nieuwe kolom A in te voegen, de kolom met namen naar de nieuwe kolom A te kopiëren, te vullen met VERT.ZOEKEN, Speciale waarden plakken en vervolgens de tijdelijke kolom A te verwijderen. Excel-professionals kunnen deze zet waarschijnlijk in hun slaap doen.
Ik ga je voorstellen de uitdaging aan te gaan en deze eenstaps-methode te proberen. Ja, je zult de formule een paar weken aan je muur moeten plakken, maar dat deed je lang geleden ook met VERT.ZOEKEN, nietwaar?
Ik denk dat de reden dat dit zo moeilijk is, is dat je twee functies gebruikt die je waarschijnlijk nooit eerder hebt gebruikt. Dus laat me het opsplitsen in twee stukken.
Ten eerste is er de functie INDEX (). Dit is een vreselijk benoemde functie. Als iemand "index" zegt, roept dat niets in mijn hoofd op dat lijkt op wat deze functie doet. Index vereist drie argumenten.
=INDEX(data range, row number, column number)
In het Engels gaat Excel naar het gegevensbereik en retourneert u de waarde op het snijpunt van de (rijnummer) de rij en de (kolomnummer) de kolom. Hé, denk er eens over na - dit is vrij eenvoudig, toch? =INDEX($A$2:$C$6,4,2)
geeft u de waarde in B5.
Het toepassen van INDEX () om ons probleem, kun je erachter dat de werknemer nummer terug te keren uit het aanbod, zou u dit gebruiken: =INDEX($A$2:$A$6,?,1)
. Eigenlijk lijkt dit stukje ervan zo triviaal dat het nutteloos lijkt. Maar als u het vraagteken vervangt door een MATCH () -functie, heeft u de oplossing.
Hier is de syntaxis:
=MATCH(Value, Single-column data range, FALSE)
Het vertelt Excel: "Zoek het gegevensbereik en vertel me het relatieve rijnummer waar u een overeenkomst voor (gegevens) vindt. Dus om te achterhalen in welke rij de werknemer in A10 staat, zou u gebruiken =MATCH(A10,$B$2:$B$6,FALSE)
. Ja, dit is complexer dan Index , maar het zou precies in het straatje van VERT.ZOEKEN pro's moeten passen. Als A10 "Miller, Bob" bevat, dan zal deze WEDSTRIJD aangeven dat hij zich in de 3e rij van de reeks B2: B6 bevindt.
Daar is het - de MATCH () -functie vertelt de Index-functie in welke rij hij moet zoeken - je bent klaar. Neem de Index-functie, vervang ons vraagteken door de MATCH-functie en je kunt nu het equivalent van VERT.ZOEKEN doen als het sleutelveld niet in de linkerkolom staat. Hier is de functie die u kunt gebruiken:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Het plakbriefje aan mijn muur laat het eigenlijk zien als twee regels. Eerst schreef ik de uitleg voor MATCH (). Daaronder schreef ik de uitleg voor INDEX (). Ik heb toen een trechtervorm tussen de twee getekend om aan te geven dat de functie MATCH () naar het 2e argument van de functie INDEX () valt.
De eerste paar keer dat ik een van deze moest doen, kwam ik in de verleiding om gewoon een nieuwe tijdelijke kolom A erin te slaan, maar ik had de pijn om het op deze manier te doen. Het is sneller en vereist minder manipulatie. Dus de volgende keer dat u wenst dat u een negatief getal in de functie VERT.ZOEKEN kunt invoeren, kunt u deze vreemde combinatie van INDEX en MATCH proberen om uw problemen op te lossen.