Vad att veta
- Skapa först en INDEX-funktion och starta sedan den kapslade MATCH-funktionen genom att ange argumentet Lookup_value.
- Lägg sedan till argumentet Lookup_array följt av argumentet Match_type, och ange sedan kolumnintervallet.
- Vänd sedan den kapslade funktionen till en matrisformel genom att trycka Ctrl+ Shift+ Enter. Lägg slutligen till söktermerna i kalkylbladet.
Den här artikeln förklarar hur man skapar en uppslagsformel som använder flera kriterier i Excel för att hitta information i en databas eller datatabell med hjälp av en matrisformel. Matrisformeln innebär att MATCH-funktionen kapslar inuti INDEX-funktionen. Informationen omfattar Excel för Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 och Excel för Mac.
Följ med handledningen
För att följa stegen i denna handledning, skriv in exempeldata i följande celler, som visas i bilden nedan. Raderna 3 och 4 lämnas tomma för att anpassa arrayformeln som skapades under denna handledning. (Observera att den här handledningen inte inkluderar formateringen som visas i bilden.)
- Ange det översta dataintervallet i cellerna D1 till F2.
- Ange det andra området i cellerna D5 till F11.
Skapa en INDEX-funktion i Excel
Funktionen INDEX är en av få funktioner i Excel som har flera former. Funktionen har en Array Form och en Reference Form. Array Form returnerar data från en databas eller datatabell. Referensformuläret anger cellreferens eller plats för data i tabellen.
I den här handledningen används Array Form för att hitta namnet på leverantören för titan widgets, snarare än cellreferensen till denna leverantör i databasen.
Följ dessa steg för att skapa INDEX-funktionen:
- Välj cell F3 för att göra den till den aktiva cellen. Den här cellen är där den kapslade funktionen kommer att anges.
-
Gå till Formler.
Image - Välj Lookup & Reference för att öppna rullgardinsmenyn för funktioner.
- Välj INDEX för att öppna dialogrutan Select Arguments.
- Välj array, row_num, column_num.
- Välj OK för att öppna dialogrutan Function Arguments. I Excel för Mac öppnas Formula Builder.
- Placera markören i Array textrutan.
-
Markera celler D6 till F11 i kalkylbladet för att ange intervallet i dialogrutan.
Lämna dialogrutan Funktionsargument öppen. Formeln är inte klar. Du fyller i formeln i instruktionerna nedan.
Image
Starta Nested MATCH-funktionen
När man kapslar en funktion inuti en annan är det inte möjligt att öppna den andra eller kapslade funktionens formelbyggare för att ange nödvändiga argument. Den kapslade funktionen måste anges som ett av argumenten för den första funktionen.
När man anger funktioner manuellt separeras funktionens argument från varandra med ett kommatecken.
Det första steget för att ange den kapslade MATCH-funktionen är att ange argumentet Lookup_value. Lookup_value är platsen eller cellreferensen för söktermen som ska matchas i databasen.
The Lookup_value accepterar endast ett sökkriterium eller en term. För att söka efter flera kriterier utökar du Lookup_value genom att sammanfoga, eller sammanfoga, två eller flera cellreferenser med et-tecken (&).
- I dialogrutan Function Arguments placerar du markören i textrutan Row_num..
- Ange MATCH(.
- Välj cell D3 för att ange den cellreferensen i dialogrutan.
- Ange & (et-tecken) efter cellreferensen D3 för att lägga till en andra cellreferens.
-
Välj cell E3 för att ange den andra cellreferensen.
-
Ange , (ett kommatecken) efter cellreferensen E3 för att slutföra inmatningen av MATCH-funktionens Lookup_value-argument.
Image I det sista steget i handledningen kommer Lookup_values att matas in i cellerna D3 och E3 i arbetsbladet.
Slutför funktionen Nested MATCH
Detta steg omfattar att lägga till argumentet Lookup_array för den kapslade MATCH-funktionen. Lookup_array är intervallet av celler som MATCH-funktionen söker för att hitta argumentet Lookup_value som lades till i det föregående steget i handledningen.
Eftersom två sökfält identifierades i Lookup_array-argumentet, måste samma sak göras för Lookup_array. MATCH-funktionen söker bara i en array för varje angiven term. Om du vill ange flera arrayer använder du et-tecken för att sammanfoga arrayerna.
-
Placera markören i slutet av data i textrutan Row_num. Markören visas efter kommatecken i slutet av den aktuella posten.
- Markera celler D6 till D11 i kalkylbladet för att ange intervallet. Detta intervall är den första matrisen som funktionen söker efter.
- Ange & (ett et-tecken) efter cellreferenserna D6:D11. Denna symbol gör att funktionen söker efter två arrayer.
- Markera celler E6 till E11 i kalkylbladet för att ange intervallet. Detta intervall är den andra arrayen som funktionen söker efter.
-
Ange , (ett kommatecken) efter cellreferensen E3 för att slutföra inmatningen av MATCH-funktionens Lookup_array-argument.
Image - Lämna dialogrutan öppen för nästa steg i handledningen.
Lägg till MATCH-typargumentet
Det tredje och sista argumentet i MATCH-funktionen är argumentet Match_type. Detta argument talar om för Excel hur man matchar Lookup_value med värden i Lookup_array. De tillgängliga alternativen är 1, 0 eller -1.
Detta argument är valfritt. Om den utelämnas använder funktionen standardvärdet 1.
- Om Match_type=1 eller utelämnas, hittar MATCH det största värdet som är mindre än eller lika med Lookup_value. Lookup_array-data måste sorteras i stigande ordning.
- Om Match_type=0, hittar MATCH det första värdet som är lika med Lookup_value. Lookup_array-data kan sorteras i valfri ordning.
- Om Match_type=-1, hittar MATCH det minsta värdet som är större än eller lika med Lookup_value. Lookup_array-data måste sorteras i fallande ordning.
Ange dessa steg efter kommatecken som angavs i föregående steg på raden Row_num i INDEX-funktionen:
- Ange 0 (en nolla) efter kommatecken i textrutan Row_num. Detta nummer gör att den kapslade funktionen returnerar exakta matchningar till termerna som anges i cellerna D3 och E3.
-
Ange ) (en avslutande parentes) för att slutföra MATCH-funktionen.
Image - Lämna dialogrutan öppen för nästa steg i handledningen.
Avsluta INDEX-funktionen
MATCH-funktionen är klar. Det är dags att flytta till textrutan Column_num i dialogrutan och ange det sista argumentet för INDEX-funktionen. Detta argument talar om för Excel att kolumnnumret ligger i intervallet D6 till F11. Det här intervallet är där den hittar informationen som returneras av funktionen. I det här fallet en leverantör för titanwidgets.
- Placera markören i textrutan Column_num.
-
Ange 3 (siffran tre). Detta nummer talar om för formeln att söka efter data i den tredje kolumnen i intervallet D6 till F11.
Image - Lämna dialogrutan öppen för nästa steg i handledningen.
Skapa matrisformeln
Innan du stänger dialogrutan, förvandla den kapslade funktionen till en matrisformel. Denna array tillåter funktionen att söka efter flera termer i datatabellen. I den här handledningen matchas två termer: Widgets från kolumn 1 och Titanium från kolumn 2.
För att skapa en matrisformel i Excel, tryck på CTRL, SHIFT och ENTERtangenter samtidigt. När den väl har tryckts in omges funktionen av hängslen, vilket indikerar att funktionen nu är en array.
- Välj OK för att stänga dialogrutan. I Excel för Mac väljer du Klar.
- Välj cell F3 för att visa formeln, placera sedan markören i slutet av formeln i formelfältet.
- För att konvertera formeln till en array, tryck CTRL+ SHIFT+ ENTER.
- A N/A-fel visas i cell F3. Det här är cellen där funktionen skrevs in.
-
N/A-felet visas i cell F3 eftersom cellerna D3 och E3 är tomma. D3 och E3 är cellerna där funktionen letar efter Lookup_value. Efter att data har lagts till i dessa två celler ersätts felet med information från databasen.
Image
Lägg till sökkriterier
Det sista steget är att lägga till söktermerna i kalkylbladet. Det här steget matchar termerna Widgets från kolumn 1 och Titanium från kolumn 2.
Om formeln hittar en matchning för båda termerna i lämpliga kolumner i databasen, returnerar den värdet från den tredje kolumnen.
- Välj cell D3.
- Enter Widgets.
- Välj cell E3.
- Skriv Titanium och tryck Enter.
- Leverantörens namn, Widgets Inc., visas i cell F3. Detta är den enda listade leverantören som säljer Titanium Widgets.
-
Välj cell F3. Funktionen visas i formelfältet ovanför kalkylbladet.
{=INDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
I det här exemplet finns det bara en leverantör för titanwidgets. Om det hade funnits mer än en leverantör, returneras den leverantör som anges först i databasen av funktionen.
Image
