Sök upp närmaste matchningsvärde med flera kriterier
I vissa fall kan du behöva slå upp det närmaste eller ungefärliga matchningsvärdet baserat på mer än ett kriterium. Med kombinationen av INDEX, MATCH och IF funktioner kan du snabbt få det gjort i Excel.
Hur slår man upp närmast matchande värde med mer än ett kriterium?
Som skärmdumpen nedan visas måste du hitta rätt person för jobbet baserat på två kriterier "the major är Dator” och ”arbetserfarenheten är 15 år ”.
Anmärkningar: För att detta ska fungera korrekt, om det innehåller dubbla majors, bör arbetserfarenheterna för dessa dubbla majors sorteras i stigande ordning.
1. Välj en tom cell för att mata ut resultatet, kopiera sedan formeln nedan till den och tryck på ctrl + shift + ange för att få resultatet.
=INDEX(D3:D8,MATCH(G5,IF(B3:B8=G4,C3:C8),1))
Anmärkningar: i denna formel:
- D3: D8 är kolumnintervallet innehåller resultatet du söker efter;
- G5 innehåller det andra kriteriet (erfarenhet nummer 15) du kommer att slå upp värde baserat på;
- G4 innehåller de första kriterierna (Dator) som du kommer att slå upp värde baserat på;
- B3: B8 är intervallet av celler som matchar de första kriterierna;
- C3: C8 är intervallet av celler som matchar det andra kriteriet;
- Antal 1 är ungefärlig uppslagning, vilket betyder att om det exakta värdet inte kan hittas, kommer det att hitta det största värdet som är mindre än uppslagsvärdet;
- Denna formel måste anges som en matrisformel med ctrl + shift + ange nycklar.
Hur den här formeln fungerar
Denna formel kan delas upp i flera komponenter:
- IF(B3:B8=G4,C3:C8): IF-funktionen returnerar här resultatet som {9;13;FALSE;FALSE;FALSE;FALSE}, som kommer från att testa värdena i B3:B8 för att se om de matchar värdet i G4. Om det finns en matchning returneras motsvarande värde, annars returneras FALSE. Här hittar två matcher och fyra felmatchningar.
- En matrisformel =MATCH(G5,{9;13;FALSE;FALSE;FALSE;FALSE},1): MATCH-funktionen hittar positionen för nummer 15 (värdet i G5) i området C3:C8. Eftersom talet 15 inte kan hittas matchar det näst minsta värdet 13. Så resultatet här är 2.
- Och =INDEX(D3:D8,2): Funktionen INDEX returnerar värdet för den andra cellen i området D3:D8. Så slutresultatet är Amy.
Relaterade funktioner
Excel IF-funktion
IF-funktionen är en av de enklaste och mest användbara funktionerna i Excel-arbetsboken. Det utför ett enkelt logiskt test som beror på jämförelseresultatet och returnerar ett värde om ett resultat är SANT, eller ett annat värde om resultatet är FALSKT.
Excel MATCH-funktion
Microsoft Excel MATCH-funktionen söker efter ett specifikt värde i ett cellområde och returnerar den relativa positionen för detta värde.
Excel INDEX-funktion
INDEX-funktionen returnerar det visade värdet baserat på en viss position från ett intervall eller en matris.
Relaterade artiklar
Genomsnittliga celler baserat på flera kriterier
I Excel kanske de flesta av oss är bekanta med funktionerna COUNTIF och SUMIF, de kan hjälpa oss att räkna eller summera värden utifrån kriterier. Men har du någonsin försökt beräkna medelvärdet av värden utifrån ett eller flera kriterier i Excel? Den här handledningen ger exempel och formler i detaljer för att hjälpa dig att enkelt få det gjort.
Klicka för att veta mer ...
Räkna celler om ett av flera kriterier uppfylls
Denna handledning delar sätten att räkna celler om de innehåller X eller Y eller Z ... etc. i Excel.
Klicka för att veta mer ...
Räkna unika värden baserat på flera kriterier
Den här artikeln tar några exempel för att räkna unika värden baserat på ett eller flera kriterier i ett kalkylblad med detaljerade metoder steg för steg.
Klicka för att veta mer ...
De bästa Office-produktivitetsverktygen
Kutools för Excel - Hjälper dig att sticka ut från mängden
Kutools för Excel har över 300 funktioner, Se till att det du behöver bara är ett klick bort...
Fliken Office - Aktivera läsning och redigering av flikar i Microsoft Office (inkluderar Excel)
- En sekund att växla mellan dussintals öppna dokument!
- Minska hundratals musklick åt dig varje dag, säg adjö till mushanden.
- Ökar din produktivitet med 50 % när du visar och redigerar flera dokument.
- Ger effektiva flikar till Office (inklusive Excel), precis som Chrome, Edge och Firefox.
