Hoe vlookup in excel omkeren met index-match?

Om te begrijpen hoe INDEX en MATCH werken als formule voor reverse lookup, moet u één simpel ding kiezen: Match vertelt de index de positie (celnummer) van een waarde in een kolom of rij, waarna de index deze waarde retourneert. waarde met behulp van deze positie (celnummer) .

Zie het zo: de MATCH-functie is een undercoveragent die de crimineel opspoort en de INDEX-functie is een agent die vervolgens die crimineel arresteert.

Maar laten we in detail leren hoe we deze twee functies kunnen combineren. Hieronder vindt u de syntaxis van INDEX zoals u weet.

INDEX(matrix, rij_getal, [kolom_getal])

In de INDEX-functie vertelt het argument row_num uit welke rij de waarde moet worden geretourneerd. Stel dat u 4 invoert, dan wordt de waarde van de vierde rij geretourneerd.

Om de omgekeerde opzoekformule te maken, moeten we MATCH vervangen door row_argument van INDEX.

Wanneer we MATCH gebruiken, wordt gezocht naar de waarde in de zoekkolom en wordt het celnummer van die waarde geretourneerd. En vervolgens gebruikt INDEX dit nummer om de celpositie uit de waardekolom te bepalen.

Uiteindelijk retourneert het de waarde van die cel en krijgt u de waarde waarnaar u op zoek bent. Maar laten we nu met een echt voorbeeld werken. Hieronder hebben we een lijst met steden en de namen van de werknemers die daar werken.

Hier moeten we zoeken naar de naam van de werknemer die in Mumbai werkt. Als u nu naar de gegevens kijkt, in de kolom met steden, is dat onze opzoekkolom, en in de kolom met namen van werknemers is dat uw waardekolom. En de formule zal zijn:

 =INDEX(B2:B11,MATCH(E3,A2:A11,0),0)

Laten we deze formule in twee delen opsplitsen om het te begrijpen.

Deel 1 : In het eerste deel gebruikten we de matchfunctie om te zoeken naar de waarde “Mumbai” en deze retourneerde de “5”, wat de positie is van de cel waarin je de waarde “Mumbai” hebt in de kolom met steden.

Deel 2 : In het tweede deel gebruikten we INDEX en verwezen we naar de kolom met de werknemersnaam om de waarde te vinden. Hier weet de indexfunctie dat u de waarde van de 5e cel van de kolom wilt hebben. Het leverde dus “Siya” op in het resultaat.

Meer voorbeelden van INDEX en matching

We hebben enkele veelvoorkomende problemen voor ons liggen die we hebben opgelost met behulp van de INDEX MATCH-formule. Toegang tot de voorbeeldbestanden: Zorg ervoor dat u deze voorbeeldbestanden hier downloadt, zodat u ze bij elk voorbeeld kunt volgen.

1. Eenvoudig zoeken met INDEX – MATCH

Een normale zoekopdracht is een van de belangrijkste taken die u met zoekformules moet uitvoeren en INDEX MATCH is daar perfect voor. Hier hebben we een gegevenstabel met werknemers-ID en naam. Elke ID is uniek en u moet zoeken naar de naam van de werknemer met zijn ID.

Stel dat u wilt zoeken naar de naam EMP-132. Hiervoor wordt de formule:

 =INDEX(name_column,MATCH(emp-id,emp-id_column,0))

zo werkt deze formule

Eerst komt MATCH overeen met de emp-id in de emp-id-kolom en retourneert het celnummer van de id die u zoekt. Hier is het regelnummer 6.

Daarna retourneert INDEX de werknemersnaam uit de naamkolom met hetzelfde celnummer.

2. Kijk naar links

VERT.ZOEKEN kan niet naar links gaan tijdens het zoeken naar een waarde. Zoals ik al zei, kun je in INDEX en MATCH in elke richting zoeken. In de onderstaande gegevenstabel ziet u na de bedragkolom de kolom met het factuurnummer.

Als u dus het bedrag van een bepaalde rekening wilt opzoeken, is dit niet mogelijk met VERT.ZOEKEN. Wanneer u in VERT.ZOEKEN een tabel selecteert, is de eerste kolom in die tabel de opzoekkolom.

Maar hier in deze tabel moeten we de laatste kolom van de tabel gebruiken als opzoekkolom. Dus duim omlaag voor VERT.ZOEKEN hier. Laten we INDEX en MATCH bellen voor redding en de formule zal zijn:

 =INDEX(G2:G14,MATCH(L6,J2:J14,0),0)

…dit is hoe deze formule werkt

• Allereerst verwees u naar de bedragkolom in de indexfunctie. Dit is de kolom waaruit we de waarde moeten halen.
• Ten tweede hebt u in het argument row_number van de indexfunctie de matchfunctie gebruikt en het factuurnummer opgegeven, naar de factuurkolom verwezen en nul gebruikt voor exacte match.
• Ten derde retourneert de matchfunctie het celnummer van de factuur uit het bereik.

En aan het einde gebruikt INDEX dit nummer om het bedrag terug te geven door de cel uit de bedragkolom te positioneren.

3. FuzzySearch

Net als VLOOKUP kunt u ook INDEX/MATCH gebruiken voor een ruwe zoekopdracht.

Fuzzy zoeken kan handig zijn als de waarde die u zoekt niet in de lijst staat en u de beste overeenkomst wilt krijgen. In de onderstaande tabel vind je een lijst met cijfers op basis van cijfers.

En als u een score van 79 wilt behalen, kunt u de onderstaande formule gebruiken.

 =INDEX(B2:B6,MATCH(D3,A2:A6,1))

…dit is hoe deze formule werkt

In deze formule hebben we 1 gebruikt in de matchfunctie voor het match_type , waardoor een zoekopdracht bij benadering kan worden uitgevoerd. Het retourneert de eerste waarde die kleiner is dan of gelijk is aan de zoekwaarde.

Voor 79 is de eerste laagste waarde 75, en voor 75 is het cijfer B. Daarom krijg je een B in de uitslag.

4. Horizontaal ZOEKEN

Zoals je weet is HLOOKUP bedoeld voor horizontaal zoeken, maar je kunt daarvoor ook INDEX en MATCH gebruiken. Hier in de onderstaande gegevenstabel heeft u een horizontale tabel voor de maandelijkse verkopen en wilt u de verkoopwaarde voor ‘mei’ verkrijgen.

En de formule zal zijn:

 =INDEX(amount,0,MATCH(lookup_month,months,0))

…dit is hoe deze formule werkt

In de bovenstaande formule gebruikten we MATCH in het argument row_num van de index in plaats van MATCH in column_num. En match retourneert het kolomnummer van mei.

En dan retourneert INDEX de waarde van de resultaatkolom op basis van het positienummer.

5. Tweerichtingszoeken

Bij een tweerichtingszoekopdracht moeten we een waarde uit een tabel halen. Kijk maar eens naar onderstaande tabel waar u de verkoopbedragen per gebied en per product vindt.

Als u nu het verkoopbedrag van een product voor een bepaald gebied wilt weten, moet u in twee richtingen zoeken en daarvoor moet u een combinatie van INDEX MATCH MATCH gebruiken. Ja, hier moet je MATCH twee keer gebruiken.

Bij een normale combinatie van INDEX en MATCH gebruik je MATCH voor het rijnummer, maar bij tweerichtingszoeken moet je het ook voor het kolomnummer gebruiken. De formules zullen zijn:

 =INDEX(A1:F5,MATCH(H3,A1:F1,0),MATCH(H2,A1:A5,0))

…dit is hoe deze formule werkt

Om in de bovenstaande formule het verkoopbedrag uit de tabel te halen, gebruikte u de indexfunctie en vervolgens de matchfunctie voor de argumenten row_num en column_num. De matchfunctie, het argument column_num, retourneert 5 als de Product-D-waarde in de 5e rij van het bereik waarnaar u verwijst.

En de matchfunctie gevonden in het argument row_num retourneert 2 omdat de waarde van de noordelijke zone zich in de tweede kolom bevindt van het bereik waarnaar u verwijst.

Met deze waarden retourneerde de indexfunctie nu de waarde die in de tweede kolom en de vijfde rij staat: 1456.

6. Hoofdlettergevoelig

Als u een probleem tegenkomt wanneer u twee dezelfde waarden in een lijst of kolom heeft, maar in verschillende hoofdletters en kleine letters, kunt u hoofdlettergevoelig zoeken om de juiste waarde te vinden. Laten we eens kijken naar de lijst met studenten hieronder waar je de voornaam hebt staan en in de tweede kolom die je hebt gemarkeerd.

En in het begin zijn er zelfstandige naamwoorden die hetzelfde zijn, maar in verschillende tekstgevallen. Bijvoorbeeld John Parker en JOHN Mathew. Stel dat u wilt zoeken naar de merken ‘JOHN’ en niet ‘John’. U kunt dan een exacte matchzoekopdracht maken met INDEX en MATCH. En de formule zal zijn:

 =INDEX(B2:B21,MATCH(TRUE,EXACT(E5,A2:A21),0))

…dit is hoe deze formule werkt

Hier in deze formule heb je de EXACT-functie in de match-functie gebruikt. Omdat de matchfunctie niet naar een hoofdlettergevoelige waarde kan zoeken en EXACT hiervoor een perfecte functie is.

Het kan twee waarden vergelijken en TRUE retourneren als ze precies hetzelfde zijn (inclusief hoofdlettergebruik), maar u moet deze formule in tabelvorm invoeren omdat u de hele kolom met één enkele waarde in EXACT moet vergelijken. Wanneer u het invoert, wordt een array zoals deze geretourneerd.

 =INDEX(Sheet1!$B$2:$B$21,MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},0))

Van daaruit moet u de matchfunctie gebruiken om de positie van TRUE uit de array te halen.

Op dit punt heeft u het celnummer (rijnummer) van de waarde die u moet vinden. En u kunt INDEX gebruiken voor deze kolomwaarde.

Belangrijk punt: als u met EXACT meer dan één WAAR krijgt, retourneert de match eenvoudigweg het nummer voor de eerste WAAR

7. Met wildcards bij INDEX MATCH

Wildcards zijn erg handig. U kunt een gedeeltelijke zoekopdracht uitvoeren met behulp van jokertekens . En het beste is dat u, net als bij alle andere formulezoekopdrachten, ook jokertekens kunt gebruiken bij index en match.

Kijk maar eens naar de namenlijst hieronder, waar u de voor- en achternaam van de medewerkers en hun leeftijden vindt.

Uit deze lijst moet u de leeftijd van een bepaalde werknemer (Sondra) halen. Maar feit is dat je alleen de voornaam kent.

En als u een asterisk gebruikt, kunt u op de voornaam naar Sondra’s leeftijd zoeken. Hiervoor wordt de formule:

 =INDEX(B2:B11,MATCH("Sondra*",A2:A11,0),0)

…dit is hoe deze formule werkt

Een asterisk is een jokerteken dat n aantal tekens kan vervangen. Dus als je het achter de voornaam gebruikte, verving het de achternaam.

8. Laagste waarde

Stel dat u een lijst met leerlingen heeft met hun scores, zoals hieronder. En nu wil je vanuit deze lijst zoeken naar de naam van de student die de laagste score heeft.

Hiervoor kunt u de MIN-functie gebruiken met index en match en de formule zal zijn:

 =INDEX(A2:A21,MATCH(MIN(B2:B21),B2:B21,0))

Je hebt de namen van de studenten in kolom A en hun scores in kolom B.

Wanneer u deze formule dus in een cel invoegt en op Enter drukt, wordt de naam geretourneerd van de leerling met de laagste score, namelijk Librada Bastian.

Uitleg

In deze formule hebben we drie verschillende delen.

In het eerste deel retourneert de MIN-functie de laagste score.

Daarna retourneert de matchfunctie in het tweede deel de cel voor die laagste score.

Uiteindelijk retourneert de indexfunctie de waarde van de kolom met de leerlingnaam, waarbij dezelfde celpositie wordt gebruikt die door de match wordt geretourneerd.

Tip: Op dezelfde manier kun je ook de naam krijgen van de leerling die de hoogste score haalt.

9. Beste n-de score

Denk nu zo: je hebt een lijst met studenten met hun examenscores en uit deze lijst wil je de naam halen van de student die de op één na hoogste score heeft behaald.

Het punt is dat je niet weet wat de op een na hoogste score is.

Wanneer u met formulezoekopdrachten naar een waarde zoekt, weet u normaal gesproken zeker welke waarde u zoekt. Maar hier weet je niet wat de op een na hoogste score is.

Hiervoor kun je dus een grote functie combineren met een index en deze matchen. De grote functie helpt u bij het bepalen van de op een na hoogste waarde in het bereik.

En de formule zal zijn:

 =INDEX(student_names,MATCH(LARGE(score,2),score,0))

…dit is hoe deze formule werkt

In deze formule hebt u de grote functie binnen de matchfunctie gebruikt voor het argument lookup_value. En in de grote functie noemde je het scorebereik en 2 om de op een na hoogste waarde te krijgen.

Nadat de grote functie de op een na grootste waarde heeft geretourneerd, gebruikt de match-functie die waarde en retourneert het overeenkomstige celnummer.

En uiteindelijk gebruikt de indexfunctie dit celnummer en retourneert de naam van de student.

10. Meerdere criteria

Normaal gesproken is de combinatie van index en match bedoeld om naar één enkele waarde te zoeken. En daarom gebruik je in de matchfunctie slechts één bereik.

Maar soms, wanneer u in de echte wereld met gegevens wordt geconfronteerd, moet u meerdere criteria gebruiken om een waarde te vinden.

Beschouw het onderstaande voorbeeld. Hier heeft u een lijst met producten met verschillende details, zoals productnaam, categorie en maat.

En uit deze gegevens wilt u de prijs van een bepaald product halen op basis van alle criteria.

De formule zal daarom zijn:

 =INDEX(D2:D11,MATCH(1,(A14=A2:A11)*(B14=B2:B11)*(C14=C2:C11),0))

Opmerking: dit is een matrixformule, dus u moet deze invoeren met ctrl + shift + enter.

…dit is hoe deze formule werkt

In deze formule heb je drie verschillende arrays die overeenkomen met drie verschillende waarden en deze arrays retourneren TRUE en FALSE waar de waarden overeenkomen.

Als je ze daarna met elkaar vermenigvuldigt, krijg je een array of iets dergelijks.

 =INDEX(D2:D11,MATCH(1,{0;0;0;0;0;0;1;0;0;0},0))

En de matchfunctie retourneert de positie van 1 voor de array.

Aan het einde retourneert INDEX de prijs van de prijskolom met behulp van het getal dat door de wedstrijd wordt geretourneerd.

Tip: Als u geen matrixformule wilt gebruiken, kunt u de voorwaarde SUMPRODUCT gebruiken.

11. Eerste numerieke waarde van een bereik

Stel dat u een lijst heeft waarin u zowel tekst- als numerieke waarden heeft, en nu wilt u uit deze lijst de eerste numerieke waarde halen.

Om dit te doen, kunt u de ISNUMBER-functie combineren met index/match. ISNUMBER kan u helpen bepalen welke waarde een getal is en welke tekst.

De formule zal zijn:

 =INDEX(list,MATCH(TRUE,ISNUMBER(list),0))

U moet deze formule in tabelvorm invoeren (met behulp van Ctrl + Shift + Enter).

…dit is hoe deze formule werkt

In deze formule retourneert ISNUMBER een array die gelijk is aan de lengte van de lijst en in deze array heb je TRUE voor de waarden die getallen zijn en FALSE voor de rest van de waarden.

Daarna gebruikte je TRUE in de matchfunctie als zoekwaarde. Het retourneert dus het positienummer van de eerste WAAR in de array.

Uiteindelijk retourneert het gebruik van deze positienummerindex de eerste numerieke waarde.

12. Haal de eerste niet-lege waarde op

Laten we het zo bekijken: u heeft een lijst met waarden waarin enkele van de eerste cellen leeg zijn en u wilt de eerste niet-lege waarde verkrijgen.

En u kunt deze formule gebruiken om deze eerste niet-lege waarde te krijgen.

 =INDEX(A2:A11,MATCH(FALSE,ISBLANK(A2:A11),0))

…dit is hoe deze formule werkt

We moeten deze formule in drie verschillende delen verdelen om deze verstandig te begrijpen.

Eerst gebruikte je de ISBLANK-functie in de match-functie om een array te krijgen waarin je TRUE hebt voor lege cellen en FALSE voor niet-lege cellen.

Ten tweede retourneert MATCH het positienummer van de eerste TRUE in de array die door ISBLANK wordt geretourneerd.

Op dit punt heeft u dus het celnummer van de eerste niet-lege waarde.

Ten derde retourneert de indexfunctie eenvoudigweg de eerste niet-lege waarde in de lijst.

13. Meest voorkomende tekst

Stel nu dat u, gegeven een lijst met tekstwaarden, de meest voorkomende tekst moet tellen.

In de onderstaande lijst staan namen.

Maar er zijn enkele namen die meer dan eens voorkomen.

Nu moet u dus de naam verkrijgen die het hoogste voorkomt in de lijst. U kunt de onderstaande formule gebruiken, die een combinatie is van MODE, INDEX en MATCH.

 =INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0)))

Wanneer u deze formule invoegt, wordt ‘Tamesha’ geretourneerd, wat de meest voorkomende naam is.

…dit is hoe deze formule werkt

Eerst vergelijkt MATCH de hele reeks namen met zichzelf. En daarbij zal het een array retourneren waarin elke tekst zijn eerste positie vertegenwoordigt.

Laten we een voorbeeld nemen van de naam “Tamesha”, onze meest voorkomende naam op de lijst. Als je nu naar de lijst kijkt, gebeurde dit eerst in de 8e cel en daarna in de 12e cel.

Maar als je naar de tabel kijkt, is voor alle posities waar we “Tamesha” hebben, een 8 teruggegeven, wat de eerste positie is.

Daarna zal de modusfunctie, uit de door de MATCH geretourneerde array, het meest voorkomende getal retourneren, namelijk het celnummer van de eerste keer dat ” Tamesha ” voorkomt.

En aan het einde retourneert INDEX de tekst met dit celnummer.

14. Maak een hyperlink

Laten we nu zeggen dat u naast het zoeken naar een waarde ook een hyperlink voor die waarde wilt maken. Zo navigeer je snel naar de cel waar jouw zoekkolom zich bevindt.

In de onderstaande tabel moet u bijvoorbeeld de leeftijd van een persoon achterhalen. En als u een hyperlink voor deze waarde maakt, kunt u eenvoudig naar de cel navigeren waar deze waarde zich bevindt.

En hiervoor moeten we HYPERLINK + Cell gebruiken met INDEX en MATCH en de formule zal zijn:

Dit is hoe deze formule werkt

Laten we deze formule in verschillende delen verdelen om deze beter te begrijpen.

• Allereerst gebruikte je index en match in de celfunctie. En als u beide functies in de celfunctie gebruikt, krijgt u een celverwijzing van de overeenkomstige waarde in plaats van de overeenkomstige waarde.
• Ten tweede hebt u “#” samengevoegd met de celverwijzing.
• Ten derde heb je index en match opnieuw gebruikt om de overeenkomende waarde te verkrijgen die je als linktekst kunt gebruiken. Op deze manier heb je de bijbehorende waarde en de link naar de cel waar die waarde zich bevindt.