Opslag med flere kriterier med INDEX og MATCH
Når man behandler en stor database i et Excel -regneark med flere kolonner og rækketekster, er det altid svært at finde noget, der opfylder flere kriterier. I dette tilfælde kan du bruge en matrixformel med the INDEX , MATCH fforeninger.
Hvordan udfører jeg et opslag med flere kriterier?
For at finde ud af produkt der er hvid , medium-størrelse med en pris på $18 som vist på billedet ovenfor, kan du drage fordel af den boolske logik til at generere en matrix på 1'er og 0'er for at vise de rækker, der opfylder kriterierne. MATCH -funktionen finder derefter placeringen af den første række, der opfylder alle kriterierne. Derefter finder INDEX det tilsvarende produkt -id på den samme række.
Generisk syntaks
=INDEX(return_range,MATCH(1,(criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),0))
√ Bemærk: Dette er en matrixformel, der kræver, at du indtaster med Ctrl + Flytte + Indtast.
- returområde: Det område, hvor du vil have kombinationsformlen til at returnere produkt -id'et fra. Her refererer til produkt -ID -serien.
- kriterier_værdi: Kriterierne, der bruges til at lokalisere produkt -id'ets placering. Her refereres til værdierne i cellerne H4, H5 og H6.
- kriterieområde: De tilsvarende intervaller, hvor kriterier_værdier er opført. Her refererer til farve, størrelse og prisklasser.
- match_type 0: Tvinger MATCH til at finde den første værdi, der nøjagtigt er lig med opslagsværdi.
At finde det produkt, der er hvid , medium-størrelse med en pris på $18, kopier eller indtast formlen herunder i cellen H8, og tryk på Ctrl + Flytte + Indtast for at få resultatet:
= INDEX (B5: B10, MATCH (1, ("Hvid"=C5: C10) * ("Medium"=D5: D10) * (18=E5: E10),0))
Eller brug en cellereference til at gøre formlen dynamisk:
= INDEX (B5: B10, MATCH (1, (H4=C5: C10) * (H5=D5: D10) * (H6=E5: E10),0))
Forklaring af formlen
=INDEX(B5:B10,MATCH(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))
- (H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10): Formlen sammenligner farven i cellen H4 mod alle farverne i området C5: C10; sammenligner størrelsen i H5 mod alle størrelser i D5: D10; sammenligner prisen i H6 mod alle priser i E5: E10. Det første resultat er sådan her:
{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}*{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE}*{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}.
Multiplikationen vil omdanne SAND og FALSK til 1 og 0:
{1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
Efter multiplikation vil vi have et enkelt array som dette:
{0;0;0;0;1;0}. - MATCH (1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0) = MATCH (1,{0;0;0;0;1;0}, 0): Match_type 0 beder MATCH -funktionen om at finde det nøjagtige match. Funktionen returnerer derefter positionen til 1 i arrayet {0;0;0;0;1;0}, Hvilket er 5.
- INDEKS(B5: B10,MATCH (1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0)) = INDEX (B5: B10,5): INDEX -funktionen returnerer 5værdi i produkt-id-området B5: B10, Hvilket er 30005.
Relaterede funktioner
Excel INDEX -funktionen returnerer den viste værdi baseret på en given position fra et område eller en matrix.
Funktionen Excel MATCH søger efter en bestemt værdi i et celleområde og returnerer værdiens relative position.
Relaterede formler
Opslag nærmeste matchværdi med flere kriterier
I nogle tilfælde skal du muligvis søge den nærmeste eller omtrentlige matchværdi baseret på mere end ét kriterium. Med kombinationen af INDEX-, MATCH- og IF -funktioner kan du hurtigt få det gjort i Excel.
Omtrentlig match med INDEX og MATCH
Der er tidspunkter, hvor vi skal finde omtrentlige kampe i Excel for at evaluere medarbejdernes præstationer, bedømme elevernes score, beregne porto baseret på vægt osv. I denne vejledning vil vi tale om, hvordan man bruger INDEX- og MATCH -funktionerne til at hente resultater, vi har brug for.
Opslagsværdier fra et andet regneark eller en anden projektmappe
Hvis du ved, hvordan du bruger VLOOKUP -funktion til at søge efter værdier i et regneark, vil vlookup -værdier fra et andet regneark eller en projektmappe ikke være et problem for dig. Tutorialen viser dig, hvordan du vlookup -værdier fra et andet regneark i Excel.
De bedste Office-produktivitetsværktøjer
Kutools til Excel - Hjælper dig med at skille dig ud fra mængden
Kutools til Excel kan prale af over 300 funktioner, At sikre, at det, du har brug for, kun er et klik væk...
Fanen Office - Aktiver læsning og redigering af faner i Microsoft Office (inkluderer Excel)
- Et sekund til at skifte mellem snesevis af åbne dokumenter!
- Reducer hundreder af museklik for dig hver dag, og farvel med musens hånd.
- Øger din produktivitet med 50 %, når du ser og redigerer flere dokumenter.
- Giver effektive faner til Office (inkluderer Excel), ligesom Chrome, Edge og Firefox.
