Note: The other languages of the website are Google-translated. Back to English

Hur returnerar jag flera matchande värden baserat på ett eller flera kriterier i Excel?

Normalt är det enkelt för de flesta av oss att leta upp ett visst värde och returnera det matchande objektet med hjälp av VLOOKUP-funktionen. Men har du någonsin försökt att returnera flera matchande värden baserat på ett eller flera kriterier enligt följande skärmdump? I den här artikeln kommer jag att presentera några formler för att lösa denna komplexa uppgift i Excel.

Returnera flera matchande värden baserat på ett eller flera kriterier med matrisformler


Returnera flera matchande värden baserat på ett eller flera kriterier med matrisformler

Till exempel vill jag extrahera alla namn vars ålder är 28 år och kommer från USA, använd följande formel:

1. Kopiera eller skriv in formeln nedan i en tom cell där du vill hitta resultatet:

=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Anmärkningar: I ovanstående formel, B2: B11 är den kolumn som matchande värde returneras från; F2, C2: C11 är det första villkoret och kolumndata som innehåller det första villkoret; G2, D2: D11 är det andra villkoret och kolumndata som innehåller detta villkor, ändra dem till ditt behov.

2. Tryck sedan på Ctrl + Skift + Enter för att få det första matchningsresultatet och välj sedan den första formelcellen och dra påfyllningshanteringen ner till cellerna tills felvärdet visas. Nu returneras alla matchande värden som visas nedan:

tips: Om du bara behöver returnera alla matchande värden baserat på ett villkor, använd nedanstående matrisformel:

=IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1), ROW(1:1))),"" )


Fler relativa artiklar:

  • Returnera flera sökvärden i en kommaseparerad cell
  • I Excel kan vi använda VLOOKUP-funktionen för att returnera det första matchade värdet från en tabellceller, men ibland måste vi extrahera alla matchande värden och sedan separeras med en specifik avgränsare, som komma, bindestreck, etc ... i en enda cell som följande skärmdump visas. Hur kunde vi få och returnera flera uppslagsvärden i en kommaseparerad cell i Excel?
  • Vlookup och returnera flera matchande värden på en gång i Google Sheet
  • Den normala Vlookup-funktionen i Google-ark kan hjälpa dig att hitta och returnera det första matchande värdet baserat på en viss data. Men ibland kan du behöva slå upp och returnera alla matchande värden enligt följande skärmdump. Har du några bra och enkla sätt att lösa denna uppgift i Google-ark?
  • Vlookup och returnera flera värden från rullgardinslistan
  • I Excel, hur kan du söka efter och returnera flera motsvarande värden från en rullgardinslista, vilket innebär att när du väljer ett objekt från listrutan, visas alla dess relativa värden på en gång som följande skärmdump visas. Den här artikeln presenterar jag lösningen steg för steg.
  • Vlookup och returnera flera värden vertikalt i Excel
  • Normalt kan du använda Vlookup-funktionen för att få det första motsvarande värdet, men ibland vill du returnera alla matchande poster baserat på ett specifikt kriterium. Den här artikeln kommer jag att prata om hur man slår på och returnerar alla matchande värden vertikalt, horisontellt eller i en enda cell.
  • Vlookup och returnera matchande data mellan två värden i Excel
  • I Excel kan vi använda den normala Vlookup-funktionen för att få motsvarande värde baserat på en viss data. Men ibland vill vi söka efter och returnera matchningsvärdet mellan två värden som följande skärmdump visas, hur kan du hantera den här uppgiften i Excel?

 


  • Super Formula Bar (enkelt redigera flera rader med text och formel); Läslayout (enkelt läsa och redigera ett stort antal celler); Klistra in i filtrerat intervall...
  • Sammanfoga celler / rader / kolumner och förvaring av data; Delat cellinnehåll; Kombinera duplicerade rader och summa / genomsnitt... Förhindra duplicerade celler; Jämför intervall...
  • Välj Duplicera eller Unikt Rader; Välj tomma rader (alla celler är tomma); Super Find och Fuzzy Find i många arbetsböcker; Slumpmässigt val ...
  • Exakt kopia Flera celler utan att ändra formelreferens; Skapa referenser automatiskt till flera ark; Sätt in kulor, Kryssrutor och mer ...
  • Favorit och sätt snabbt in formler, Intervall, diagram och bilder; Kryptera celler med lösenord; Skapa e-postlista och skicka e-post ...
  • Extrahera text, Lägg till text, ta bort efter position, Ta bort mellanslag; Skapa och skriva ut personsökningstalsatser; Konvertera mellan celler innehåll och kommentarer...
  • Superfilter (spara och tillämpa filterscheman på andra ark); Avancerad sortering efter månad / vecka / dag, frekvens och mer; Specialfilter av fet, kursiv ...
  • Kombinera arbetsböcker och arbetsblad; Sammanfoga tabeller baserat på nyckelkolumner; Dela data i flera ark; Batchkonvertera xls, xlsx och PDF...
  • Gruppering av pivottabell efter veckonummer, veckodagen och mer ... Visa olåsta, låsta celler av olika färger; Markera celler som har formel / namn...
kte-flik 201905
  • Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
  • Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
  • Ökar din produktivitet med 50 % och minskar hundratals musklick för dig varje dag!
officetab botten

 

Sortera kommentarer efter
Kommentarer (25)
Inga betyg än. Bli först med att betygsätta!
Denna kommentar minimerades av moderatoren på webbplatsen
Jag försökte exakt samma formel; kopieras till 100%. Det enda jag ändrade var att data matchades och returnerades. När jag använder den här formeln säger Excel "Du har angett för många argument för den här funktionen).=INDEX('2020 Volymrapport'!$B$3:$B$100,SMALL(IF(COUNTIF($A$1,'2020 Volym Rapport'!$A$3:$A$100)*COUNTIF($A$3,'2020 volymrapport'!$D$3:$D$100),RAD('2020 volymrapport'!$A$3:$G$100)- MIN(RAD('2020 volymrapport'!$A$3:$G$100))+1,"0"),RAD(A1),KOLUMN(A1))
Denna kommentar minimerades av moderatoren på webbplatsen
Hej, Kan du ge dina data och formelfel som en skärmdump här?
Denna kommentar minimerades av moderatoren på webbplatsen
Hej, Hur jag kan använda den för horisontellt tillstånd.
Denna kommentar minimerades av moderatoren på webbplatsen
Vad är "0" efter +1:an i formeln? Det är inte i exemplet.
Denna kommentar minimerades av moderatoren på webbplatsen
Hej jag hade provat samma formel. Jag får resultat men när du ger CSE ger det inga flera svar
Denna kommentar minimerades av moderatoren på webbplatsen

Denna kommentar minimerades av moderatoren på webbplatsen
Angående returnera multipla matchande värden baserade på ett eller flera kriterier med matrisformler: Varför är det så att om jag har data någon annanstans förutom att börja i A1 så fungerar det inte även om jag uppdaterar alla cellreferenser i formeln?
Denna kommentar minimerades av moderatoren på webbplatsen
I det första exemplet, vilken förändring av formeln skulle behövas för att returnera alla som var yngre än 28 år?
Denna kommentar minimerades av moderatoren på webbplatsen
Hej,

Jag undrade om det överhuvudtaget är möjligt att ange ett 2:a kriteriet men från samma intervall som det 1:a kriteriet,

Till exempel med det använda exemplet ovan skulle jag vilja söka efter namn på personer från både Amerika och Frankrike. Så cell F3 skulle ha Frankrike, Scarlett & Andrew skulle också fyllas i listan i kolumn G

Tack för hjälpen på förhand.
Denna kommentar minimerades av moderatoren på webbplatsen
Hej nick

Glad att hjälpa till. Om du vill få namn på personer från både Amerika och Frankrike, råder jag dig att använda vår formel två gånger för att få resultatet. Se skärmdumpen, I F2 och G2 är värdena "USA" och "Frankrike". Tillämpa formel =IFERROR(INDEX($B$2:$B$11, SMALL(OM($F$2=$D$2:$D$11, ROW($D$2:$D$11)-RAD($D$2)+1 ), ROW(1:1))),"" ) för att få resultaten för Amerika. Och använd formeln =IFERROR(INDEX($B$2:$B$11, SMALL(IF($G$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+ 1), ROW(1:1))),"" ) för att få resultaten för Frankrike. Det är enkelt. Gör ett försök.

Vänliga hälsningar,
Mandy
Denna kommentar minimerades av moderatoren på webbplatsen
När jag använder den andra formeln och drar nedåt visas ingenting. Formelresultatet (fx) säger att det borde returnera något men det är tomt. Hur korrigerar jag detta?
Denna kommentar minimerades av moderatoren på webbplatsen
Hej Alysia,

Glad att hjälpa till. Jag försökte den andra formeln i artikeln och dra formeln nedåt, resten av resultaten returnerades. Jag tror att det kan finnas två anledningar till ditt problem. Först kanske du glömmer att trycka på Ctrl + Skift + Enter för att ange formeln. För det andra är det matchande resultatet bara ett, så inga andra resultat returneras inte. Vänligen ta en check.

Vänliga hälsningar,
Mandy
Denna kommentar minimerades av moderatoren på webbplatsen
Hallå,
Jag har försökt använda formeln och den genererar antingen ett värde på 0 eller den bifogade bilden
Denna kommentar minimerades av moderatoren på webbplatsen
Hej Milku
Din skärmdump visade WPS-programvara av MAC-version, så jag är inte säker på om vår formel är tillgänglig.
Jag laddade upp en Excel-fil här, du kan försöka se om den kan räkna rätt i din miljö.
Tack!
Denna kommentar minimerades av moderatoren på webbplatsen
Hallå,
vad som skulle behövas för att utöka den första formeln i följande fall:
Vissa ID:n är tomma (t.ex. cell A5 är tom) och jag skulle vilja ha ett extra villkor som endast matar ut rader när ID:n inte är tomma. (Så resultatet bör då vara James och Abdul.
Tack!
Denna kommentar minimerades av moderatoren på webbplatsen
Hej Jo,
För att lösa ditt problem, använd följande formel:
=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11)*($A$2:$A$11<>0), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Snälla ha en ry, hoppas det kan hjälpa dig!
Denna kommentar minimerades av moderatoren på webbplatsen
Hej,

om jag skriver "Namn" i cell H1 och vill länka det till formeln, hur skulle det fungera?
Sedan kunde jag skriva "ID" i cell H1 och skulle automatiskt få som ett resultat: AA1004; DD1009; PP1023 (för den första formeln)

Tack på förhand!
Denna kommentar minimerades av moderatoren på webbplatsen
Hej Marie
Förlåt, jag kan inte förstå poängen med ditt första problem, kan du förklara ditt problem tydligare och mer detaljerat? Eller så kan du infoga en skärmdump här för att beskriva ditt problem.
När det gäller den andra frågan behöver du bara ändra cellreferensen så här:
=INDEX($A$2:$A$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Kom ihåg att trycka Ctrl + Skift + Enter nycklar tillsammans.
Försök gärna, hoppas det kan hjälpa dig!
Denna kommentar minimerades av moderatoren på webbplatsen
Hej, tack för formeln. Det fungerade för "fasta" värden/text som kriterier. Ett av kriterierna jag försöker använda är dock ett villkor (värden <>0 ), men det fungerar inte med den beskrivna formeln. Vet ni vad jag ska ändra för att anpassa formeln så att jag kan ha ett tillstånd som ett av kriterierna, tack?

Bäst,

John
Denna kommentar minimerades av moderatoren på webbplatsen
Hej Marcus
För att lösa ditt problem, läs den här artikeln:
https://www.extendoffice.com/documents/excel/6393-excel-vlookup-function.html#b3-2
Det finns några detaljerade förklaringar av denna uppgift. Du behöver bara ändra criteira till din egen.
Tack!
Denna kommentar minimerades av moderatoren på webbplatsen
Hej,

Först, tack för att du delar med dig!

Kan du ge en lösning på fallet nedan:

Jag har 3 kolumner (A: Innehåller referensinformation, B: Innehåller information som ska sökas, C: Sökresultat)

Bildens webbadress finns nedan

https://ibb.co/VHCd09K

Kolumn A-------------------------Kolumn B------------ Kolumn C
Filnamn-------------------------Namn----------------Filnamn, Dokumentnamn, Elementnamn, namn
Ändrade element-----------------Element--------------Ändrade element, elementnamn, element-ID
Kolumn plats
Dokument namn
Elementnamn
Namn
Kategori
Garanti
Lutning
Element-ID

Vad jag behöver är att söka i kolumn A efter eventuell partiell matchning med cell B2 (Namn) eller B3 (Element) och få resultatet i en cell,

Tack, Behzad
Denna kommentar minimerades av moderatoren på webbplatsen
Hej, Behzad
Kanske kan nedanstående användardefinierade funktion hjälpa dig.
Public Function ConcatPartLookUp(rngInput As Range, rngSource As Range, Optional strDelimiter As String, Optional blCaseSensitive)
Dim rng As Range
If strDelimiter = "" Then strDelimiter = ","
If IsMissing(blCaseSensitive) Then
    blCaseSensitive = False
Else
    blCaseSensitive = True
End If
For Each rng In rngSource
    If blCaseSensitive Then
        If InStr(1, rng.Value, rngInput.Value, vbBinaryCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    Else
        If InStr(1, rng.Value, rngInput.Value, vbTextCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    End If
Next
If Len(ConcatPartLookUp) > 0 Then ConcatPartLookUp = Mid(ConcatPartLookUp, 2, Len(ConcatPartLookUp))
End Function


Efter att ha kopierat och klistrat in den här koden och använd sedan den här formeln:=ConcatPartLookUp(B2,$A$2:$A$8) för att få det resultat du behöver.
Gör ett försök, hoppas det kan hjälpa dig!
Denna kommentar minimerades av moderatoren på webbplatsen
Hej,

Tack för att du publicerade dessa exempel.
Jag försöker implementera detta i mitt eget ark, men får det inte att fungera (kanske för att jag använder en europeisk version av excel)?

Jag vill få datum för de dagar då jag hade mina skift eller att jag har arbetat "några" (>0) timmar för en kund.

Så i I3 står namnet och i J3 månaden. K3 och L3 är skift (1 är arbetat) och timmar (vet inte hur man ställer in detta, bör vara mer än noll)

Mina förväntade resultat är i:
Skift: I7 och I8
timmar: J7

Så jag arbetade mer än 0 timmar för 'person 2' i oktober den 3-10-2022
hade skift för person 2 10-10-2022 och 28-10-2022

När jag lägger till '=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW ($A$2:$D$11)-MIN(RAD($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))' i mitt excelark tillåter det inte komma mellan de olika delarna av formeln.
Så jag måste ändra dem till ';'.
Men när jag provar det står det alltid: '#NAME?'

Så kan någon hjälpa mig med detta?

Vänliga hälsningar,

Bas
[img]https://drive.google.com/file/d/1iIPQKuj_PNhqWyWlwJ4IQTqGNEd6B9Hw/view?usp=share_link[/img]
Denna kommentar minimerades av moderatoren på webbplatsen
Hej, om det finns dubbletter av värden (t.ex. två adams), hur ser jag till att det bara returnerar 1 adam och inte 2?
Denna kommentar minimerades av moderatoren på webbplatsen
Hej Bobby,
För att bara extrahera unika matchande värden bör du använda följande formel:
När du har klistrat in formeln trycker du på Ctrl + Skift + Enter för att få rätt resultat.
=FEL(INDEX($B$2:$B$5, MATCH(0, ANTALOM(H1:$H$1, $B$2:$B$5)+OM($D$2:$D$5<>$G$2, 1 , 0)+OM($C$2:$C$5<>$F$2, 1, 0), 0)), "")

Gör ett försök, hoppas det kan hjälpa dig!
Det finns inga kommentarer här ännu
Lämna dina kommentarer
Postar som gäst
×
Betygsätt detta inlägg:
0   Tecken
Föreslagna platser

Följ oss

Copyright © 2009 - www.extendoffice.com. | Alla rättigheter förbehållna. Drivs av ExtendOffice. | | Sitemap
Microsoft och Office-logotypen är varumärken eller registrerade varumärken som tillhör Microsoft Corporation i USA och / eller andra länder.
Skyddad av Sectigo SSL