VLOOKUP, HLOOKUP och INDEX-MATCH är kända och vanliga sätt att leta upp värden i Excel-tabellen. Men det här är inte de enda sätten att leta upp värden i Excel. I den här artikeln lär vi oss hur du använder OFFSET -funktionen tillsammans med MATCH -funktionen i Excel. Ja du läste rätt, vi kommer att använda den ökända OFFSET -funktionen i Excel för att leta upp ett visst värde i en Excel -tabell.
Generisk formel,
| = OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0)) |
Läs detta noga:
StartCell:Detta är startcellen i uppslagstabellen. Låt oss säga att om du vill leta i intervall A2: A10, då är StartCell A1.
RowLookupValue: Detta är det uppslagsvärde som du vill hitta i rader underStartCell.
RowLookupRange:Detta är intervallet där du vill leta upp RowLookupValue. Det är intervallet nedan StartCell (A2: A10).
ColLookupValue: Detta är det sökningsvärde som du vill hitta i kolumner (rubriker).
ColLookupRange:Detta är intervallet där du vill leta upp ColLookupValue. Det är intervallet på höger sida av StartCell (som B1: D1).
Så nog med teorin. Låt oss börja med ett exempel.
Exempel: Slå upp försäljning med hjälp av OFFSET och MATCH -funktion från Excel -tabell
Här har vi en provtabell över försäljning gjord av olika anställda under olika månader.
Nu ber vår chef oss att ge försäljningen av Id 1004 i juni månad. Det finns många sätt att göra det men eftersom vi lär oss om OFFSET-MATCH-formeln kommer vi att använda dem för att leta upp önskat värde.
Vi har redan den generiska formeln ovan. Vi behöver bara identifiera dessa variabler i den här tabellen.
StartCell är B1 här. RowLookupValue är M1. RowLookupRange är B2: B1o (intervall under startcellen).
ColLookupValue är i M2 -cell. ColLookupRange är C1: I1 (Header Range till höger om StartCellen).
Vi har identifierat alla variabler. Låt oss lägga dem i en Excel -formel.
Skriv denna formel i Cell M3 och tryck på enter -knappen.
| = OFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)) |
När du trycker på enter -knappen får du resultatet direkt. Försäljningen gjord ID 1004 i juni månad är 177.
Hur fungerar det?
OFFSET -funktionens arbete är att flytta bort från den givna startcellen till den givna raden och kolumnerna och sedan returnera värdet från den cellen. Till exempel, om jag skriver OFFSET (B1,1,1), går OFFSET -funktionen till cell C2 (1 cell ned, 1 cell till höger) och returnerar värdet.
Här har vi formelnOFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)).
Den första MATCH -funktionen returnerar indexet för M1 (1004) i intervallet B2: B10, vilket är 4. Nu är formeln,OFFSET (B1,4, MATCH (M2, C1: I1,0)).
Nästa MATCH -funktion returnerar indexet för M2 ('Jun') i intervall C1: I1, vilket i 7. Nu är formelnOFFSET (B1,4,7).
Nu flyttar OFFSET -funktionen helt enkelt 4 celler ner till cellen B1 som tar den till B5. Därefter flyttas OFFSET -funktionen till 7 vänster till B5, vilket tar den till I5. Nu räcker det. OFFSET -funktionen returnerar värde från cell I5 som 177.
Fördelar med denna uppslagsteknik?
Det här är snabbt. Den enda fördelen med denna metod är att den är snabbare än de andra metoderna. Samma sak kan göras med INDEX-MATCH-funktionen eller VLOOKUP-funktionen. Men det är bra att veta några alternativ. Det kan komma till nytta någon dag.
Så ja killar, så här kan du använda OFFSET och MATCH -funktionen för att SÖKA upp värden i Excel -tabeller. Jag hoppas att det var tillräckligt förklarande. Om du har några tvivel angående denna artikel eller något annat Excel/VBA -relaterat ämne, fråga mig i kommentarfältet nedan.
Använd INDEX och MATCH för att leta upp värde:INDEX-MATCH-formeln används för att leta dynamiskt och exakt ett värde i en given tabell. Detta är ett alternativ till VLOOKUP -funktionen och det övervinner bristerna i VLOOKUP -funktionen.
Summa med OFFSET -grupper i rader och kolumner: OFFSET -funktionen kan användas för att summera grupp av celler dynamiskt. Dessa grupper kan vara var som helst i arket.
Så här hämtar du var tionde värde i ett intervall i Excel: Med hjälp av OFFSET -funktionen i Excel kan vi hämta värden från alternerande rader eller kolumner. Denna formel tar hjälp av ROW -funktionen för att alternera genom rader och COLUMN -funktionen för att alternera i kolumner.
Så här använder du OFFSET -funktionen i Excel: OFFSET -funktionen är en kraftfull Excel -funktion som underskattas av många användare. Men experter vet kraften i OFFSET -funktionen och hur kan vi använda den här funktionen för att utföra några magiska uppgifter i Excel -formeln. Här är grunderna för OFFSET -funktionen.
Populära artiklar:
50 Excel -genvägar för att öka din produktivitet | Bli snabbare på din uppgift. Dessa 50 genvägar får dig att arbeta ännu snabbare med Excel.
Hur man använder Excel VLOOKUP -funktion| Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.
Hur man använder Excel COUNTIF -funktion| Räkna värden med förhållanden med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifika värden. COUNTIF -funktionen är avgörande för att förbereda din instrumentpanel.
Hur man använder SUMIF -funktionen i Excel | Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.