Använd INDEX med två MATCH-funktioner med exakt matchning för att returnera antalet där en resursrad möter en formatkolumn. Den här guiden skapar ett litet kalkylblad för bildresurser, förklarar formeln och visar ett alternativ med nästlade XLOOKUP-funktioner för Mac-versioner som stöds.

Vilken uppslagsmetod fungerar i Excel för Mac?

I den här uppgiften har du ett resursnamn, till exempel Dot Grid, och ett format, till exempel PNG. Du vill ha det enda antalet där de möts. Detta kallas ofta för ett tvåvägsuppslag.

Microsoft anger att INDEX och MATCH är tillgängliga i Excel för Microsoft 365 för Mac, Excel 2024 för Mac och Excel 2021 för Mac. Båda metoderna i den här artikeln fungerar inom det dokumenterade stödet för Mac. Se Microsofts dokumentation om INDEX-funktionen och dokumentation om MATCH-funktionen.

XLOOKUP är också dokumenterad för dessa Mac-utgåvor. Microsoft anger uttryckligen att funktionen inte är tillgänglig i Excel 2016 och Excel 2019, trots att de utgåvorna förekommer i sidans bredare lista över tillämpliga versioner. Om din utgåva saknar XLOOKUP använder du metoden med INDEX och MATCH. Microsoft: XLOOKUP-funktionen

Börja med INDEX och MATCH om du vill granska rad- och kolumnpositionerna var för sig medan du lär dig. Välj nästlade XLOOKUP om du redan använder den funktionen och föredrar dess standardinställning för exakt matchning.

Skapa ett litet rutnät med antal bildresurser

Ange följande syntetiska data i A1:D5 i ett tomt kalkylblad. Den vänstra tabellkolumnen nedan motsvarar kalkylbladskolumn A; lägg inte till en extra kolumn för radnummer. Antalen är exempelvärden.

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

Varje resurs upptar en rad och varje format en kolumn. I det här exemplet använder du unika resursnamn och unika formatrubriker. Ange en uttrycklig numerisk nolla när det kända antalet är noll; lämna antal som inte är fastställda för kontroll innan du använder rutnätet.

Skapa ett separat uppslagsområde på samma kalkylblad:

Cell Ange
F1 Resurs att söka efter
F2 Dot Grid
G1 Format att söka efter
G2 PNG
H1 Antal

De tre källområdena har olika uppgifter: A2:A5 innehåller resursnamn, B1:D1 innehåller formatrubriker och B2:D5 innehåller endast antal. Om rubrikerna ligger utanför antalsområdet stämmer de relativa positionerna.

Returnera antalet med INDEX och två MATCH-funktioner

Markera H2, ange hela formeln nedan och tryck på Retur:

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

Resultatet ska vara 24: antalet för Dot Grid i kolumnen PNG. Microsofts anvisningar för Mac beskriver hur du markerar en resultatcell, anger en formel som börjar med = och trycker på Retur. Microsoft: Ange en formel

Förstå de två positionerna

Den första MATCH hittar resursens position i A2:A5; den andra hittar formatets position i B1:D1. Den sista 0 begär en exakt matchning. Utan det argumentet använder MATCH som standard ungefärlig matchning med ett krav på sorteringsordning. Microsoft: MATCH-funktionen

För de angivna värdena blir de två inre beräkningarna:

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

Resultat: 2.

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

Resultat: 2.

Hela formeln väljer alltså rad 2, kolumn 2 i antalsblocket:

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

Det är kalkylbladscell C3, som innehåller 24. INDEX använder positioner inom det angivna området, så dess andra rad är kalkylbladsrad 3 här. Microsoft dokumenterar att när båda positionerna anges returneras värdet där de möts. Microsoft: INDEX-funktionen

Lås källområdena när du kopierar

Dollartecknen låser källområdena. Indatareferenserna F2 och G2 förblir relativa, så när du kopierar H2 ned till H3 läser nästa formel F3 och G3 medan samma källrutnät används. På Mac kan du också markera en referens i formelfältet och trycka på Command+T för att växla mellan referenstyper. Microsoft: Växla mellan relativa och absoluta referenser

Ange till exempel Tile Arc i F3 och JPG i G3, och kopiera sedan H2 till H3. Det nya resultatet ska vara 6. Då får du en liten begärandelista utan att ändra det ursprungliga resursrutnätet.

Använd nästlade XLOOKUP-funktioner i Mac-versioner som stöds

Med samma kalkylblad och uppslagsvärden kan du i stället ange den här formeln i H2:

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

Den returnerar 24 för Dot Grid och PNG. Microsofts dokumentation innehåller ett exempel med nästlad XLOOKUP som utför både en lodrät och en vågrät sökning för att returnera ett skärningsvärde. Exakt matchning är standard. Microsoft: XLOOKUP-funktionen

I det här genomarbetade exemplet väljer det inre uppslaget kolumnen PNG med antal: 18, 24, 9 och 14. Det yttre uppslaget väljer värdet som hör till Dot Grid. Om du använder samma tre källområden blir det enkelt att jämföra metoden med den tidigare formeln.

Kontrollera resultatet innan du använder ett större rutnät

Använd följande fall som en enkel kontroll. Ändra F2 och G2 för varje rad och jämför sedan H2 med det förväntade resultatet. Återställ Dot Grid och PNG efteråt om du vill behålla det ursprungliga exemplet.

Resursvärde Formatvärde Förväntat resultat Kontroll
Dot Grid PNG 24 Skärning i mitten
Tile Arc JPG 6 Första resursen och sista formatet
Soft Square SVG 7 Sista resursen och första formatet
Loop Ribbon JPG 0 Uttryckligt nollantal
Tile Arcs PNG #N/A Resursnamn saknas
Dot Grid TIFF #N/A Formatrubrik saknas

Om ett tal är fel bör du kontrollera områdesgränserna innan du ändrar uppslagsmetoden. I det här rutnätet måste A2:A5 motsvara de fyra raderna i B2:D5, och B1:D1 måste motsvara dess tre kolumner. Om du till exempel tar med A1 endast i resursens uppslagsområde förskjuts den beräknade positionen från den avsedda antalsraden.

När du utökar just det här rutnätet med ytterligare en resurs på rad 6 uppdaterar du både resursområdet till A2:A6 och antalsblocket till B2:D6. När du lägger till ett format i kolumn E uppdaterar du både rubrikområdet till B1:E1 och antalsblocket till B2:E5. Dessa ändringar i par bevarar layouten som formlerna bygger på.

Dubbletter kräver också uppmärksamhet. MATCH med exakt matchning returnerar den första matchande positionen och skiljer inte mellan versaler och gemener; den tolkar också * och ? som jokertecken i textsökningar. Använd unika namn utan dessa tecken i den här uppsättningen. Microsoft: MATCH-funktionen

Visa ett användbart meddelande när ett namn saknas

När grunduppslaget fungerar kan du ersätta ett fel på grund av utebliven matchning med ett läsbart meddelande:

=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 returnerar meddelandet när den omslutna formeln ger #N/A; annars behålls resultatet. Funktionen är dokumenterad för Microsoft 365 för Mac, Excel 2024 för Mac och Excel 2021 för Mac. Microsoft: IFNA-funktionen

I det här exemplet ger Tile Arcs resultatet Kontrollera resurs eller format, medan Loop Ribbon med JPG fortfarande returnerar 0. Den skillnaden håller isär ett saknat namn från ett registrerat nollantal. Om en källcell själv innehåller #N/A ersätter den här omslutningen även det felet, så kontrollera källcellen när namnen verkar stämma.

För daglig användning bör du hålla resursnamnen unika, bevara de tre källområdenas inbördes placering och kontrollera några kända skärningspunkter efter att du ändrat rutnätet. Sedan kan du hämta antal för begärda bildresurser genom att ändra resurs- och formatvärdena.

Källor