I den här artikeln lär vi oss hur man LOOKUP -värde mellan två nummer i Excel.
Scenario:
Det är lätt att slå upp ett värde med ett kriterium i en tabell. Vi kan helt enkelt använda VLOOKUP. Men vad kan vi göra om vi har flera kolumnkriterier att matcha i dina data och behöver leta upp i flera kolumner för att matcha ett värde. Låt oss lära oss hur detta problem kan lösas med hjälp av olika sökversioner av excel
LOOKUP -värde mellan två nummer
Använda VLOOKUP -formeln
I VLOOKUP ungefärlig matchning, om ett värde inte hittas matchas det sista värdet som är mindre än uppslagsvärdet och värdet från det angivna kolumnindexet returneras.
Generisk formel för LOOKUP -värde mellan två nummer:
| = VLOOKUP (värde, tabell, lookup_col, 1) |
Och en sak till med Vlookup är att det letar efter värdet i kolumnen och om det inte hittar värdet i kolumnmatrisen matchar det och returnerar värdet som är mindre än det värdet i tabellmatrisen.
Obs! VLOOKUP gör som standard en ungefärlig matchning om vi utelämnar variabeln intervalluppslagning. En ungefärlig matchning är användbar när du vill göra en ungefärlig matchning och när du har din tabellmatris sorterad i stigande ordning.
Använda INDEX och MATCH -formeln
INDEX- och MATCH -formeln gör samma arbete som ovan men med olika syntax. Men om du bara skulle välja den med vilka funktioner du är bekväm med
Generisk formel till LOOKUP -värde mellan två nummer
| = INDEX (uppslagningsområde, MATCH (1, INDEX ((kriterier1 = intervall1)*(kriterier2 = intervall2),0,1),0)) |
lookup_range: intervallet från vilket du vill hämta värdet.
Kriterier1, Kriterier2, Kriterier N: Dessa är de kriterier du vill matcha inom område1, intervall2 och intervall N. Du kan ha upp till 270 kriterier - intervallpar.
Område1, intervall2, intervallN: Dessa är de områden där du kommer att matcha dina respektive kriterier.
Exempel:
Allt detta kan vara förvirrande att förstå. Låt oss förstå hur man använder funktionen med hjälp av ett exempel. Här har vi student -ID och deras respektive betyg i ett test. Nu var vi tvungna att få betygen för varje elev genom att slå upp i betygssystemtabellen.
För att göra det kommer vi att använda ett attribut för VLOOKUP -funktionen, vilket är om VLOOKUP -funktionen inte hittar den exakta matchningen det bara letar efter ungefärlig matchning i tabellen och bara om funktionens sista argument är antingen SANN eller 1.
Tabellen för poäng / poäng måste vara stigande ordning
Använd formeln:
| = VISNING (B2, tabell, 2, 1) |
Hur fungerar det?
Vlookup -funktionen letar upp värdet 40 i markeringskolumnen och returnerar motsvarande värde om det matchas. Om den inte matchar letar funktionen efter det lägre värdet än uppslagsvärdet (dvs 40) och returnerar resultatet.
Här heter table_array intervallet som tabell i formel och B2 ges som cellreferens.
Som ni ser fick vi betyget för den första eleven. För att få alla andra betyg använder vi genväg Ctrl + D eller använd alternativet dra ner i Excel.
Här är alla betyg i klassen som använder VLOOKUP -funktionen.
LOOKUP -värde mellan två nummer på medarbetartabellen
Vi har en tabell som innehåller detaljer om alla anställda i en organisation på ett separat blad. Den första kolumnen innehåller dessa anställdas ID. Jag har kallat denna tabell för emp_data.
Nu måste mitt sökblad hämta den anställdes information vars ID är skrivet i cell B3. Jag har namngett B3 som ID.
För enkel förståelse är alla kolumnrubriker i exakt samma ordning som emp_datatabellen.
Skriv nu nedanstående formel i cell C3 för att hämta zonen för medarbetar -IDet skrivet i B3.
| = VLOOKUP (ID, Emp_Data, 2,0) |
Detta returnerar zonen för medarbetar-ID 1-1830456593 eftersom kolumn nummer 2 i databasen innehåller zonen för anställda.
Kopiera denna formel i resten av cellerna och ändra kolumnnumret i formeln för att få all information från anställda.
Du kan se all information relaterad till nämnda ID i Cell B3. Vilket id du än skriver i Cell B3 kommer all information att hämtas utan att någon formel ändras.
Hur fungerar detta?
Det finns inget knepigt. Vi använder helt enkelt VLOOKUP -funktionen för att leta upp ID och sedan hämta den nämnda kolumnen. Öva VLOOKUP med att använda sådana data för att förstå VLOOKUP mer.
Hämta anställdas data med rubriker
I exemplet ovan hade vi alla kolumner organiserade i samma ordning men det kommer att finnas tillfällen då du har en databas som kommer att ha hundratals kolumner. I sådana fall är den här metoden för att hämta information om anställda inte bra. Det blir bättre om formeln kan titta på kolumnrubriken och hämta data från den kolumnen från medarbetartabellen.
Så för att hämta värde från tabellen med hjälp av kolumnrubrik använder vi en 2-vägs uppslagningsmetod eller säger dynamisk kolumn VLOOKUP.
Använd denna formel i cell C3 och kopiera i resten celler. Du behöver inte ändra någonting i formeln, allt hämtas från thd emp_data.
| = VLOOKUP (ID, Emp_Data, MATCH (C2, Emp_Data_Headers, 0), 0) |
Denna formel hämtar helt enkelt all information från matchade kolumner. Du kan blanda ihop rubrikerna i rapporten, det gör ingen skillnad. Oavsett vilken rubrik som skrivs i cellen ovan innehåller respektive data.
Hur fungerar detta?
Detta är helt enkelt en dynamisk VLOOKUP. Du kan läsa om det här. Om jag ska förklara det här blir det en för stor artikel.
Hämta medarbetar -ID med delvis matchning
Det kan hända att du inte kommer ihåg hela ID -en för en anställd, men du vill fortfarande hämta informationen om något ID. I sådana fall är delvis matchning VLOOKUP den bästa lösningen.
Till exempel, om jag vet att någon id innehåller 2345 men jag vet inte hela Id. Om jag anger det här numret i cell C3 kommer utgången att vara som.
Vi får ingenting. Eftersom ingenting matchar 2345 i tabellen. Ändra ovanstående formel så här.
| =VLOOKUP("*"&ID&"*", Emp_Data,MATCH(C2, Emp_Data_Headers, 0), 0) |
Kopiera detta i hela raden. Och nu har du informationen från den första medarbetaren som innehåller detta nummer.
Observera att vi får det första ID som innehåller det matchade numret i kolumnen Emp Id. Om något annat ID innehåller samma nummer kommer denna formel inte att hämta den anställdes information.
Om du vill få alla medarbetar -id: er som innehåller samma nummer använder du formeln som letar upp alla matchade värden.
Använda INDEX och MATCH -formeln
Här har vi en datatabell. Jag vill dra kundens namn med bokningsdatum, byggare och område. Så här har jag tre kriterier och ett uppslagsområde.
Skriv denna formel i cell I4 tryck enter.
| = INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)) |
Hur det fungerar:
Vi vet redan hur INDEX och MATCH fungerar i EXCEL, så jag tänker inte förklara det här. Vi kommer att prata om tricket vi använde här.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Huvuddelen är detta. Varje del av detta uttalande returnerar en grupp av true false.
När booleska värden multipliceras returnerar de en array med 0 och 1. Multiplikation fungerar som en OCH -operator. Hense när allt värde är sant först då returnerar det 1 annat 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Detta kommer helt tillbaka
| {FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; SANN; SANN
{FALSKT; FALSKT; FALSKT; SANT; SANT; SANT; SANT; SANT; FALSKT {FALSKT; FALSKT; FALSKT; SANT; FALSKT; FALSKT; FALSKT |
Som kommer att översättas till
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): INDEX -funktionen returnerar samma array ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) till MATCH -funktion som uppslagningsarray.
MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): MATCH -funktionen letar efter 1 i array {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. Och returnerar indexnumret för de första 1 som finns i gruppen. Vilket är 8 här.
INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Slutligen returnerar INDEX värdet från det angivna intervallet (E2: E16) vid hittat index (8).
Här är alla observationsanteckningar med hjälp av formeln i Excel
Anmärkningar:
- Kolumnen för poäng / poäng måste vara i stigande ordning annars kan funktionen ge fel resultat.
- VLOOKUP -funktionen letar efter värdet i den första raden i Table_array och extraherar motsvarande värden bara till höger om lookup_range.
- VLOOKUP -funktionens sista argument för funktionen måste ställas in på antingen SANT eller 1 för att få den ungefärliga matchningen.
- VLOOKUP -funktionen returnerar ett fel om arbetsbokens adress är ogiltig eller fel.
- VLOOKUP -funktionen returnerar ett fel om värdet inte matchas.
Hoppas den här artikeln om How to LOOKUP value between two numbers in Excel är förklarande. Hitta fler artiklar om beräkning av värden och relaterade Excel -formler här. Om du gillade våra bloggar, dela dem med dina vänner på Facebook. Och du kan också följa oss på Twitter och Facebook. Vi vill gärna höra från dig, låt oss veta hur vi kan förbättra, komplettera eller förnya vårt arbete och göra det bättre för dig. Skriv till oss på e -postwebbplatsen.
Sätt att använda Vlookup -funktionen i datavalidering : Begränsa användare till att tillåta värden från uppslagstabellen med rutan Datavalidering i Excel. Formelbox i datavalidering låter dig välja vilken typ av begränsning som krävs.
Så här hämtar du det senaste priset i Excel : Det är vanligt att uppdatera priser i alla företag och att använda de senaste priserna för alla köp eller försäljningar är ett måste. För att hämta det senaste priset från en lista i Excel använder vi LOOKUP -funktionen. LOOKUP -funktionen hämtar det senaste priset.
VLOOKUP -funktion för att beräkna betyg i Excel : Att beräkna betyg IF och IFS är inte de enda funktionerna du kan använda. VLOOKUP är mer effektiv och dynamisk för sådana villkorliga beräkningar. För att beräkna betyg med VLOOKUP kan vi använda denna formel.
17 saker om Excel VLOOKUP : VLOOKUP används oftast för att hämta matchade värden men VLOOKUP kan göra mycket mer än så. Här är 17 saker om VLOOKUP som du bör veta för att använda effektivt.
SÖK den första texten från en lista i Excel : VLOOKUP -funktionen fungerar bra med jokertecken. Vi kan använda detta för att extrahera det första textvärdet från en given lista i excel. Här är den generiska formeln.
LOOKUP -datum med sista värdet i listan : För att hämta datumet som innehåller det sista värdet använder vi LOOKUP -funktionen. Denna funktion söker efter cellen som innehåller det sista värdet i en vektor och använder sedan den referensen för att returnera datumet.
Populära artiklar:
50 Excel -genvägar för att öka din produktivitet : Bli snabbare på dina uppgifter i Excel. Dessa genvägar hjälper dig att öka din arbetseffektivitet i Excel.
Så här använder du VLOOKUP -funktionen i Excel : Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värden från olika intervall och ark.
Hur man använder IF -funktionen i Excel : IF -satsen i Excel kontrollerar villkoret och returnerar ett specifikt värde om villkoret är SANT eller returnerar ett annat specifikt värde om det är FALSKT.
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.
Hur man använder funktionen RÄNKNING i Excel : Räkna värden med villkor 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.