Gebruik INDEX met twee MATCH-functies voor exacte overeenkomsten om de hoeveelheid op te halen waar een assetrij en een formaAtkolom elkaar kruisen. In deze handleiding maak je een klein werkblad met kunstassets, wordt de formule uitgelegd en zie je een genest alternatief met XLOOKUP voor ondersteunde Mac-versies.

Welke opzoekmethode werkt in Excel voor Mac?

In deze taak heb je een assetnaam, zoals Dot Grid, en een formaat, zoals PNG. Je wilt de hoeveelheid op het snijpunt daarvan ophalen. Dit wordt vaak een tweerichtingszoekopdracht genoemd.

Microsoft vermeldt INDEX en MATCH als beschikbaar in Excel voor Microsoft 365 voor Mac, Excel 2024 voor Mac en Excel 2021 voor Mac. Beide methoden in dit artikel werken binnen dit gedocumenteerde Mac-bereik. Zie Microsofts documentatie over de INDEX-functie en documentatie over de MATCH-functie.

XLOOKUP wordt ook voor deze Mac-edities gedocumenteerd. Microsoft vermeldt uitdrukkelijk dat deze functie niet beschikbaar is in Excel 2016 en Excel 2019, hoewel die edities wel in de bredere lijst met toepasselijke versies op de pagina staan. Als jouw editie XLOOKUP niet ondersteunt, gebruik dan de methode met INDEX en MATCH. Microsoft: XLOOKUP-functie

Begin met INDEX en MATCH als je tijdens het leren de rij- en kolomposities afzonderlijk wilt bekijken. Kies voor geneste XLOOKUP als je die functie al gebruikt en de voorkeur geeft aan de standaardinstelling voor exacte overeenkomsten.

Stel een kleine rastertabel met hoeveelheden van kunstassets in

Voer in een leeg werkblad de volgende synthetische gegevens in A1:D5 in. De meest linkse tabelkolom hieronder staat voor werkbladkolom A; voeg geen extra kolom met rijnummers toe. Deze hoeveelheden zijn illustratief.

Asset SVG PNG JPG
Tile Arc 12 18 6
Dot Grid 8 24 10
Loop Ribbon 15 9 0
Soft Square 7 14 5

Elke asset staat op één rij en elk formaat in één kolom. Gebruik in dit voorbeeld unieke assetnamen en unieke formaAtkoppen. Voer expliciet de numerieke waarde nul in als de hoeveelheid bekend nul is; controleer hoeveelheden die nog niet zijn vastgesteld voordat je het raster gebruikt.

Maak een apart opzoekgedeelte op hetzelfde werkblad:

Cel Invoer
F1 Op te zoeken asset
F2 Dot Grid
G1 Op te zoeken formaat
G2 PNG
H1 Hoeveelheid

De drie bronbereiken hebben verschillende functies: A2:A5 bevat assetnamen, B1:D1 bevat formaAtkoppen en B2:D5 bevat alleen hoeveelheden. Als je de koppen buiten het hoeveelhedenbereik houdt, sluiten de relatieve posities goed op elkaar aan.

Haal de hoeveelheid op met INDEX en twee MATCH-functies

Selecteer H2, voer deze volledige formule in en druk op Return:

=INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0))

Het resultaat moet 24 zijn: de hoeveelheid voor Dot Grid in de kolom PNG. In de Mac-instructies van Microsoft staat dat je een resultaatcel selecteert, een formule invoert die begint met = en op Return drukt. Microsoft: Een formule invoeren

Begrijp de twee posities

De eerste MATCH bepaalt de positie van de asset binnen A2:A5; de tweede bepaalt de positie van het formaat binnen B1:D1. De laatste 0 geeft aan dat de overeenkomst exact moet zijn. Zonder dat argument gebruikt MATCH standaard benaderend zoeken, waarvoor de gegevens gesorteerd moeten zijn. Microsoft: MATCH-functie

Voor de opgegeven invoer zijn de twee berekeningen binnen de formule:

=MATCH(F2,$A$2:$A$5,0)

Resultaat: 2.

=MATCH(G2,$B$1:$D$1,0)

Resultaat: 2.

De volledige formule selecteert dus rij 2, kolom 2 binnen het hoeveelhedenblok:

=INDEX($B$2:$D$5,2,2)

Dat is werkbladcel C3, die 24 bevat. INDEX gebruikt posities binnen het opgegeven bereik, dus de tweede rij is hier werkbladrij 3. Microsoft documenteert dat het opgeven van beide posities de waarde op het snijpunt oplevert. Microsoft: INDEX-functie

Houd de bronbereiken vast wanneer je de formule kopieert

De dollartekens vergrendelen de bronbereiken. De invoerverwijzingen F2 en G2 blijven relatief, zodat als je H2 omlaag kopieert naar H3, de volgende formule F3 en G3 leest en hetzelfde bronraster gebruikt. Op een Mac kun je ook een verwijzing in de formulebalk selecteren en op Command+T drukken om tussen verwijzingstypen te wisselen. Microsoft: Wisselen tussen relatieve en absolute verwijzingen

Voer bijvoorbeeld Tile Arc in F3 in en JPG in G3, en kopieer vervolgens H2 naar H3. Het nieuwe resultaat moet 6 zijn. Zo maak je een kleine aanvraaglijst zonder het oorspronkelijke assetraster te wijzigen.

Gebruik geneste XLOOKUP in ondersteunde Mac-versies

Met hetzelfde werkblad en dezelfde opzoekinvoer kun je in plaats daarvan deze formule invoeren in H2:

=XLOOKUP(F2,$A$2:$A$5,XLOOKUP(G2,$B$1:$D$1,$B$2:$D$5))

Dit levert 24 op voor Dot Grid en PNG. De documentatie van Microsoft bevat een genest voorbeeld met XLOOKUP waarmee zowel verticaal als horizontaal wordt gezocht om een waarde op het snijpunt op te halen. Exact zoeken is de standaardinstelling. Microsoft: XLOOKUP-functie

In dit uitgewerkte voorbeeld selecteert de binnenste zoekopdracht de hoeveelhedenkolom PNG: 18, 24, 9 en 14. De buitenste zoekopdracht selecteert het item dat bij Dot Grid hoort. Als je dezelfde drie bronbereiken aanhoudt, kun je deze methode eenvoudig met de eerdere formule vergelijken.

Controleer het resultaat voordat je een groter raster gebruikt

Gebruik de volgende gevallen als een kleine acceptatietest. Wijzig F2 en G2 voor elke rij en vergelijk vervolgens H2 met het verwachte resultaat. Herstel daarna Dot Grid en PNG als je het oorspronkelijke voorbeeld wilt behouden.

Assetinvoer Formaatinvoer Verwacht resultaat Wat wordt gecontroleerd
Dot Grid PNG 24 Middelste snijpunt
Tile Arc JPG 6 Eerste asset en laatste formaat
Soft Square SVG 7 Laatste asset en eerste formaat
Loop Ribbon JPG 0 Expliciete nulhoeveelheid
Tile Arcs PNG #N/A Ontbrekende assetnaam
Dot Grid TIFF #N/A Ontbrekende formaAtkop

Als een getal niet klopt, controleer dan de grenzen van de bereiken voordat je de zoekmethode wijzigt. Voor dit raster moet A2:A5 overeenkomen met de vier rijen van B2:D5, en B1:D1 moet overeenkomen met de drie kolommen. Als je bijvoorbeeld alleen A1 toevoegt aan het assetopzoekbereik, verschuift de berekende positie ten opzichte van de bedoelde hoeveelheidsrij.

Als je dit raster uitbreidt met een asset op rij 6, werk dan zowel het assetbereik bij naar A2:A6 als het hoeveelhedenblok naar B2:D6. Als je een formaat toevoegt in kolom E, werk dan zowel het koppenbereik bij naar B1:E1 als het hoeveelhedenblok naar B2:E5. Deze wijzigingen in paren behouden de indeling die door de formules wordt gebruikt.

Let ook op dubbele namen. MATCH met exacte overeenkomsten retourneert de eerste overeenkomende positie en maakt geen onderscheid tussen hoofdletters en kleine letters; bij tekstzoekopdrachten worden * en ? ook als jokertekens geïnterpreteerd. Gebruik voor deze opzet unieke namen zonder die tekens. Microsoft: MATCH-functie

Toon een bruikbare melding bij een ontbrekende naam

Zodra de basiszoekopdracht werkt, kun je een foutmelding bij een ontbrekende overeenkomst vervangen door een duidelijke melding:

=IFNA(INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0)),"Check asset or format")

IFNA retourneert je melding wanneer de ingesloten formule #N/A oplevert; anders behoudt de functie het resultaat. De functie wordt gedocumenteerd voor Microsoft 365 voor Mac, Excel 2024 voor Mac en Excel 2021 voor Mac. Microsoft: IFNA-functie

In dit voorbeeld levert Tile Arcs de melding Asset of formaat controleren op, terwijl Loop Ribbon met JPG nog steeds 0 retourneert. Zo blijft een ontbrekende naam gescheiden van een geregistreerde nulhoeveelheid. Als een bronhoeveelheid zelf #N/A bevat, vervangt deze functie ook die foutmelding. Controleer daarom de broncel als de namen correct lijken.

Houd voor dagelijks gebruik de assetnamen uniek, behoud de uitlijning van de drie bronbereiken en controleer enkele bekende snijpunten nadat je het raster hebt gewijzigd. Vervolgens kun je elke gevraagde hoeveelheid van een kunstasset ophalen door de invoer voor asset en formaat te wijzigen.

Bronnen