In dit artikel zullen we leren hoe u de waarde tussen twee getallen in Excel kunt opzoeken.
Scenario:
Het is gemakkelijk om een waarde op te zoeken met één criterium in een tabel. We kunnen gewoon VERT.ZOEKEN gebruiken. Maar wat kunnen we doen als die criteria voor meerdere kolommen in uw gegevens overeenkomen en in meerdere kolommen moeten worden gezocht om met een waarde overeen te komen. Laten we eens kijken hoe dit probleem kan worden opgelost met behulp van verschillende opzoekversies van Excel
LOOKUP-waarde tussen twee getallen
Formule VERT.ZOEKEN gebruiken
In VERT.ZOEKEN geschatte overeenkomst, als een waarde niet wordt gevonden, wordt de laatste waarde die kleiner is dan de opzoekwaarde gevonden en wordt de waarde uit de gegeven kolomindex geretourneerd.
Algemene formule voor LOOKUP-waarde tussen twee getallen:
| = VERT.ZOEKEN (waarde, tabel, lookup_col , 1 ) |
En nog een ding over Vlookup is dat het naar de waarde in de kolom zoekt en als het de waarde in de kolomarray niet vindt, komt het overeen en retourneert het de waarde die kleiner is dan die waarde in de tabelarray.
Opmerking: VERT.ZOEKEN komt standaard bij benadering overeen, als we de zoekvariabele voor het bereik weglaten. Een geschatte overeenkomst is handig wanneer u een geschatte overeenkomst wilt doen en wanneer u uw tabelarray in oplopende volgorde hebt gesorteerd.
Formules INDEX en MATCH gebruiken
De formule INDEX en MATCH doet hetzelfde werk als hierboven, maar met een andere syntaxis. Maar als u alleen die functie zou moeten kiezen waarmee u vertrouwd bent?
Generieke formule voor LOOKUP-waarde tussen twee getallen
| =INDEX(lookup_range,OVEREENKOMST(1,INDEX((criterium1=bereik1)*(criteria2=bereik2),0,1),0)) |
lookup_range: het bereik waaruit u waarde wilt ophalen.
Criteria1, Criteria2, Criteria N: Dit zijn de criteria die u wilt matchen in bereik1, bereik2 en bereik N. U kunt maximaal 270 criteria hebben - bereikparen.
Bereik1, bereik2, bereikN: dit zijn de bereiken waarin u aan uw respectieve criteria zult voldoen.
Voorbeeld :
Al deze dingen kunnen verwarrend zijn om te begrijpen. Laten we eens kijken hoe we de functie kunnen gebruiken aan de hand van een voorbeeld. Hier hebben we Student ID en hun respectieve cijfers in een test. Nu moesten we de cijfers voor elke student ophalen door in de cijfersysteemtabel op te zoeken.
Om dit te doen, zullen we een attribuut van de VERT.ZOEKEN-functie gebruiken. Als de VERT.ZOEKEN-functie de exacte overeenkomst niet vindt, zoekt deze alleen naar de geschatte overeenkomst in de tabel en alleen als het laatste argument van de functie een van beide is WAAR of 1.
De cijfers / scoretabel moet oplopend zijn
Gebruik de formule:
| = VERT.ZOEKEN ( B2 , tabel , 2 , 1 ) |
Hoe werkt het?
De functie Vlookup zoekt de waarde 40 op in de kolom Markeringen en retourneert de overeenkomstige waarde, indien overeenkomend. Als het niet overeenkomt, zoekt de functie naar de lagere waarde dan de opzoekwaarde (d.w.z. 40) en retourneert het resultaat.
Hier wordt de table_array het bereik genoemd als tafel in formule en B2 wordt gegeven als celverwijzing.
Zoals je ziet hebben we het cijfer voor de eerste leerling gekregen. Om nu alle andere cijfers te krijgen, gebruiken we de snelkoppeling Ctrl + D of gebruik de optie om de cel naar beneden te slepen in Excel.
Hier zijn alle cijfers van de klas die de functie VERT.ZOEKEN gebruiken.
LOOKUP-waarde tussen twee getallen in de tabel met werknemers
We hebben een tabel met de gegevens van alle medewerkers in een organisatie op een apart blad. De eerste kolom bevat de ID van deze medewerkers. Ik heb deze tabel genoemd als emp_data.
Nu moet mijn zoekblad de informatie van de werknemer ophalen wiens ID in cel B3 is geschreven. Ik heb B3 als ID genoemd.
Voor een beter begrip staan alle kolomkoppen in exact dezelfde volgorde als de emp_data-tabel.
Schrijf nu de onderstaande formule in cel C3 om de zone van de werknemers-ID op te halen die in B3 is geschreven.
| =VERT.ZOEKEN(ID,Werk_gegevens,2,0) |
Hiermee wordt de zone van de werknemer-ID 1-1830456593 geretourneerd omdat kolom nummer 2 in de database de zone van werknemers bevat.
Kopieer deze formule in de rest van de cellen en wijzig het kolomnummer in de formule om alle informatie van werknemers te krijgen.
U kunt alle informatie met betrekking tot de genoemde ID zien in cel B3. Welke id u ook schrijft in cel B3, alle informatie wordt opgehaald zonder enige wijziging in de formule aan te brengen.
Hoe werkt dit?
Er is niets lastigs. We gebruiken gewoon de functie VERT.ZOEKEN om de ID op te zoeken en vervolgens de genoemde kolom op te halen. Oefen met VERT.ZOEKEN met behulp van dergelijke gegevens om de VERT.ZOEKEN beter te begrijpen.
Werknemersgegevens ophalen met behulp van koppen
In het bovenstaande voorbeeld hadden we alle kolommen in dezelfde volgorde geordend, maar er zullen momenten zijn dat u een database hebt met honderden kolommen. In dergelijke gevallen is deze methode om werknemersinformatie op te halen niet goed. Het is beter als de formule naar de kolomkop kan kijken en gegevens uit die kolom uit de werknemerstabel kan halen.
Dus om waarde uit de tabel op te halen met behulp van de kolomkop, gebruiken we een 2-way lookup-methode of zeggen we dynamische kolom VERT.ZOEKEN.
Gebruik deze formule in cel C3 en kopieer in de overige cellen. U hoeft niets in de formule te veranderen, alles wordt opgehaald uit thd emp_data.
| =VERT.ZOEKEN(ID,Emp_Data,MATCH(C2,Emp_Data_Headers,0),0) |
Deze formule haalt eenvoudig alle informatie op uit overeenkomende kolommen. U kunt de koppen in het rapport door elkaar halen, dit maakt geen verschil. Welke kop ook in de cel hierboven is geschreven, de respectieve gegevens bevatten.
Hoe werkt dit?
Dit is gewoon een dynamische VERT.ZOEKEN. Je kunt er hier over lezen. Als ik het hier uitleg, wordt het een te groot artikel.
Werknemer-ID ophalen met gedeeltelijke overeenkomst
Het kan voorkomen dat u niet het hele ID van een medewerker herinnert, maar toch de informatie van een ID wilt ophalen. In dergelijke gevallen is gedeeltelijke overeenkomst VERT.ZOEKEN de beste oplossing.
Als ik bijvoorbeeld weet dat een id 2345 bevat, maar ik weet niet de hele id. Als ik dit nummer in cel C3 invoer, is de uitvoer als volgt.
Wij krijgen niets. Aangezien niets overeenkomt met 2345 in de tabel. Pas de bovenstaande formule als volgt aan.
| =VERT.ZOEKEN("*"&ID kaart&"*",Emp_Data,BIJ ELKAAR PASSEN(C2,Emp_Data_Headers,0),0) |
Kopieer dit in de hele rij. En nu heb je de gegevens van de eerste medewerker die dit nummer bevat.
Houd er rekening mee dat we de eerste ID krijgen die het overeenkomende nummer in de kolom Emp-ID bevat. Als een andere ID hetzelfde nummer bevat, haalt deze formule de informatie van die werknemer niet op.
Als u alle werknemers-ID's wilt krijgen die hetzelfde nummer bevatten, gebruikt u de formule die alle overeenkomende waarden opzoekt.
Formules INDEX en MATCH gebruiken
Hier hebben we een gegevenstabel. Ik wil de naam van de klant ophalen met behulp van Boekingsdatum, Bouwer en Gebied. Dus hier heb ik drie criteria en één opzoekbereik.
Schrijf deze formule in cel I4 en druk op enter.
| =INDEX(E2:E16,MATCH(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0)) |
Hoe het werkt:
We weten al hoe de INDEX- en MATCH-functie werken in EXCEL, dus dat ga ik hier niet uitleggen. We zullen het hebben over de truc die we hier hebben gebruikt.
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16): Het belangrijkste onderdeel is dit. Elk deel van deze instructie retourneert een array van true false.
Wanneer booleaanse waarden worden vermenigvuldigd, wordt een array van 0 en 1 geretourneerd. Vermenigvuldiging werkt als een AND-operator. Hense wanneer alle waarden alleen waar zijn, retourneert het 1 else 0
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16) Dit zal in totaal terugkeren
| {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*
{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}* {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} |
Wat zich vertaalt in
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): INDEX-functie retourneert dezelfde array ({0;0;0;0;0; 0;0;1;0;0;0;0;0;0;0}) naar MATCH-functie als opzoekarray.
MATCH(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): MATCH-functie zoekt naar 1 in array {0;0;0; 0;0;0;0;1;0;0;0;0;0;0;0}. En retourneert het indexnummer van de eerste 1 gevonden in de array. Dat is hier 8.
INDEX(E2:E16,MATCH(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0)): Ten slotte zal INDEX terugkeren de waarde uit het opgegeven bereik (E2:E16) bij gevonden index (8).
Hier zijn alle observatienotities met behulp van de formule in Excel
Opmerkingen:
- De kolom van de tabel met cijfers/scores moet in oplopende volgorde staan, anders kan de functie een verkeerd resultaat geven.
- De functie VERT.ZOEKEN zoekt naar de waarde in de eerste rij van Table_array en extraheert overeenkomstige waarden alleen rechts van het opzoekbereik.
- Het laatste argument van de functie VERT.ZOEKEN van de functie moet worden ingesteld op TRUE of 1 om de overeenkomst bij benadering te krijgen.
- De functie VERT.ZOEKEN retourneert een fout als het adres van de werkmap ongeldig of onjuist is.
- De functie VERT.ZOEKEN retourneert een fout als de waarde niet overeenkomt.
Ik hoop dat dit artikel over de waarde ZOEKEN tussen twee getallen in Excel verhelderend is. Vind hier meer artikelen over het berekenen van waarden en gerelateerde Excel-formules. Als je onze blogs leuk vond, deel ze dan met je vrienden op Facebook. En je kunt ons ook volgen op Twitter en Facebook. We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite.
Manier om de Vlookup-functie te gebruiken in gegevensvalidatie : Beperk gebruikers om waarden uit de opzoektabel toe te staan met behulp van het vak Gegevensvalidatieformule in Excel. Met het formulevak in gegevensvalidatie kunt u het vereiste type beperking kiezen.
Hoe de laatste prijs in Excel op te halen : Het is gebruikelijk om de prijzen in elk bedrijf bij te werken en het is een must om de laatste prijzen te gebruiken voor elke aankoop of verkoop. Om de laatste prijs op te halen uit een lijst in Excel gebruiken we de LOOKUP functie. De LOOKUP-functie haalt de laatste prijs op.
VERT.ZOEKEN-functie om cijfer in Excel te berekenen : Om cijfers te berekenen zijn IF en IFS niet de enige functies die u kunt gebruiken. De VERT.ZOEKEN is efficiënter en dynamischer voor dergelijke voorwaardelijke berekeningen. Om cijfers te berekenen met VERT.ZOEKEN kunnen we deze formule gebruiken.
17 dingen over Excel VERT.ZOEKEN : VERT.ZOEKEN wordt meestal gebruikt voor het ophalen van overeenkomende waarden, maar VERT.ZOEKEN kan veel meer dan dit. Hier zijn 17 dingen over VERT.ZOEKEN die u moet weten om effectief te gebruiken.
ZOEK de eerste tekst uit een lijst in Excel : De functie VERT.ZOEKEN werkt prima met jokertekens. We kunnen dit gebruiken om de eerste tekstwaarde uit een bepaalde lijst in Excel te extraheren. Hier is de generieke formule.
LOOKUP-datum met laatste waarde in lijst : Om de datum op te halen die de laatste waarde bevat, gebruiken we de LOOKUP-functie. Deze functie zoekt naar de cel die de laatste waarde in een vector bevat en gebruikt vervolgens die verwijzing om de datum te retourneren.
Populaire artikels :
50 Excel-snelkoppelingen om uw productiviteit te verhogen : Werk sneller aan uw taken in Excel. Met deze sneltoetsen kunt u uw werkefficiëntie in Excel vergroten.
Hoe de VERT.ZOEKEN-functie in Excel te gebruiken? : Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarden op te zoeken uit verschillende bereiken en bladen.
Hoe de IF-functie in Excel te gebruiken? : De IF-instructie in Excel controleert de voorwaarde en retourneert een specifieke waarde als de voorwaarde WAAR is of retourneert een andere specifieke waarde als ONWAAR.
Hoe de SUMIF-functie in Excel te gebruiken? : Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.
Hoe de AANTAL.ALS-functie in Excel te gebruiken? : Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.