✅ Hur man gör Excel VLOOKUP skiftlägeskänsligt

Innehållsförteckning

Gör VLOOKUP skiftlägeskänsligt

Som standard är uppslagsvärdet i VLOOKUP -funktionen inte skiftlägeskänsligt. Till exempel, om ditt uppslagsvärde är MATT, matt eller Matt, är det samma sak för VLOOKUP -funktionen. Det returnerar det första matchningsvärdet oavsett fall.

Gör VLOOKUP skiftlägeskänsligt

Antag att du har data enligt nedan:

Som du kan se finns det tre celler med samma namn (A2, A4 och A5) men med olika bokstäver. Till höger (i E2: F4) har vi de tre namnen (Matt, MATT och matt) tillsammans med sina poäng i matematik.

Excel VLOOKUP-funktionen är inte utrustad för att hantera skiftlägeskänsliga uppslagsvärden. I det här exemplet ovan, oavsett vilket uppslagsvärdefall (Matt, MATT eller matt), returnerar det alltid 38 (vilket är det första matchningsvärdet).

I den här självstudien lär du dig hur du gör VLOOKUP skiftlägeskänsligt genom att:

Använda en hjälpkolumn.
Utan att använda en hjälparkolumn och använda en formel.

Gör VLOOKUP skiftlägeskänsligt - Använd hjälpkolumn

En hjälparkolumn kan användas för att få unikt uppslagsvärde för varje objekt i uppslagsmatrisen. Det hjälper till att skilja mellan namn med olika bokstäver.

Här är stegen för att göra detta:

Infoga en hjälparkolumn till vänster om kolumnen där du vill hämta data. I exemplet nedan måste du infoga hjälpkolumnen mellan kolumn A och C.
I hjälparkolumnen anger du formeln = RAD (). Det infogar radnumret i varje cell.

Använd följande formel i cell F2 för att få det skiftlägeskänsliga sökresultatet.

= VLOOKUP (MAX (EXAKT (E2, $ A $ 2: $ A $ 9)*(RAD ($ A $ 2: $ A $ 9))), $ B $ 2: $ C $ 9,2,0)

Kopiera klistra in det för de återstående cellerna (F3 och F4).

Notera: Eftersom detta är en matrisformel använder du Ctrl + Skift + Retur istället för att bara ange.

Hur fungerar detta?

Låt oss bryta ner formeln för att förstå hur det fungerar:

EXAKT (E2, $ A $ 2: $ A $ 9) - Denna del jämför uppslagsvärdet i E2 med alla värden i A2: A9. Det returnerar en matris med SANT/FALS där SANT returneras när det finns en exakt matchning. I det här fallet, där värdet i E2 är Matt, skulle det returnera följande array:
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
EXAKT (E2, $ A $ 2: $ A $ 9)*(RAD ($ A $ 2: $ A $ 9) - Den här delen multiplicerar matrisen SANT/FALS med radnumret A2: A9. Överallt där det finns en SANT, är det ger radnumret, annars ger det 0. I det här fallet skulle det returnera {2; 0; 0; 0; 0; 0; 0; 0}.
MAX (EXAKT (E2, $ A $ 2: $ A $ 9)*(RAD ($ A $ 2: $ A $ 9)))) - Denna del returnerar det maximala värdet från siffran. I det här fallet skulle det returnera 2 (vilket är radnumret där det finns en exakt matchning).
Nu använder vi helt enkelt det här numret som uppslagsvärde och använder uppslagsmatrisen som B2: C9.

Obs! Du kan infoga hjälpkolumnen var som helst i datamängden. Se bara till att det är till vänster om kolumnen där du vill hämta data. Du måste sedan justera kolumnnumret i VLOOKUP -funktionen därefter.

Om du nu inte är ett fan av hjälparkolumn kan du också göra en skiftlägeskänslig sökning utan hjälparkolumnen.

Gör VLOOKUP skiftlägeskänsligt - utan hjälpkolumnen

Även om du inte vill använda hjälparkolumnen måste du fortfarande ha en virtuell hjälpkolumn. Denna virtuella kolumn är inte en del av kalkylbladet utan är konstruerad enligt formeln (som visas nedan).

Här är formeln som ger dig resultatet utan hjälparkolumnen:

= VLOOKUP (MAX (EXAKT (D2, $ A $ 2: $ A $ 9)*(RAD ($ A $ 2: $ A $ 9))), VÄLJ ({1,2}, RAD ($ A $ 2: $ A $ 9) , $ B $ 2: $ B $ 9), 2,0)

Hur fungerar detta?

Formeln använder också begreppet en hjälparkolonn. Skillnaden är att istället för att placera hjälpkolumnen i kalkylbladet, betrakta det som virtuella hjälpdata som är en del av formeln.

Här är den del som fungerar som hjälpdata (markerad med orange):

= VLOOKUP (MAX (EXAKT (D2, $ A $ 2: $ A $ 9)*(RAD ($ A $ 2: $ A $ 9))),VÄLJ ({1,2}, RAD ($ A $ 2: $ A $ 9), $ B $ 2: $ B $ 9),2,0)

Låt mig visa dig vad jag menar med virtuella hjälpdata.

I ovanstående illustration, när jag väljer VÄLJ delen av formeln och trycker på F9, visar det resultatet som VÄLJ formeln skulle ge.

Resultatet är {2,38; 3,88; 4,57; 5,82; 6,55; 7,44; 8,75; 9,38}

Det är en matris där ett komma representerar nästa cell i samma rad och semikolon representerar att följande data finns i nästa rad. Därför skapar denna formel 2 kolumner med data - En kolumn har radnumret och en har matematikpoängen.

När du nu använder VLOOKUP -funktionen letar det helt enkelt efter uppslagsvärdet i den första kolumnen (av denna virtuella 2 -kolumndata) och returnerar motsvarande poäng. Sökvärdet här är ett tal som vi får från kombinationen MAX och EXACT -funktionen.

Ladda ner exempelfilen