Ladda ner exempelarbetsboken
Denna handledning visar hur du utför en skiftlägeskänslig sökning i Excel med två olika metoder och Google Kalkylark med en metod.
Huvudkänslig VLOOKUP med hjälpkolumn
VLOOKUP -funktion
VLOOKUP -funktionen används för att slå upp en ungefärlig eller exakt matchning för ett värde i kolumnen längst till vänster i ett område och returnerar motsvarande värde från en annan kolumn. Som standard fungerar VLOOKUP endast för icke-skiftlägeskänsliga värden så här:
| 1 | = VLOOKUP ($ E $ 2, $ B $ 2: $ C $ 4,2,0) |
Huvudkänslig VLOOKUP
Genom att kombinera VLOOKUP, EXACT, MAX och ROW kan vi skapa en skiftlägeskänslig VLOOKUP-formel som returnerar motsvarande värde för vår skiftlägeskänsliga VLOOKUP. Låt oss gå igenom ett exempel.
Vi har en lista med artiklar och deras motsvarande priser (märk att artikel-ID är skiftlägeskänsligt unikt):
Antag att vi blir ombedda att få priset för en artikel med dess artikel -ID så här:
För att uppnå detta behöver vi först skapa en hjälparkolumn med ROW:
| 1 | = RAD () |
= RAD () klicka och dra (eller dubbelklicka) för att fylla i alla rader i intervallet
Kombinera sedan VLOOKUP, MAX, EXACT och ROW i en formel så här:
| 12 | = VISNING (MAX (EXAKT (,)*(RAD ())),,, 0) |
| 1 | = VLOOKUP (MAX (EXAKT (E2, $ B $ 2: $ B $ 7)*(RAD ($ B $ 2: $ B $ 7))), $ C $ 2: $ D $ 7,2,0) |
Hur fungerar formeln?
- EXAKT -funktionen kontrollerar artikel -ID i E2 (uppslagsvärde) mot värdena i B2: B7 (uppslagningsintervall) och returnerar en matris med SANT där det finns en exakt matchning eller FLASE i en array {FLASE, FLASE, FLASE, FLASE, FLASE, TRUE}.
- Den här matrisen multipliceras sedan med RAD -matrisen {2, 3, 4, 5, 6, 7} (observera att detta matchar vår hjälparkolumn).
- MAX -funktionen returnerar det maximala värdet från den resulterande matrisen {0,0,0,0,0,7}, vilket är 7 i vårt exempel.
- Sedan använder vi resultatet som vårt uppslagsvärde i en VLOOKUP och väljer vår hjälpkolumn som uppslagsområde. I vårt exempel returnerar formeln matchningsvärdet på $ 16,00.
Metod 2 - skiftlägeskänslig VLOOKUP med "virtuell" hjälpkolumn
Denna metod använder samma logik som den första metoden, men eliminerar behovet av att skapa en hjälparkolumn och använder istället VÄLJ och RAD för att skapa en "virtuell" hjälpkolumn så här:
| 12 | = VISNING (MAX (EXAKT (,)*(RAD ())),VÄLJ ({1,2}, RAD (),),, 0) |
| 1 | = VLOOKUP (MAX (EXAKT (D2, $ B $ 2: $ B $ 7)*(RAD ($ B $ 2: $ B $ 7))), VÄLJ ({1,2}, RAD ($ B $ 2: $ B $ 7) , $ C $ 2: $ C $ 7), 2,0) |
Hur fungerar formeln?
- Den första delen av formeln fungerar på samma sätt som den första metoden.
- Genom att kombinera VÄLJ och RAD returneras en matris med två kolumner, en för radnumret och en annan för priset. Matrisen separeras med ett semikolon för att representera nästa rad och ett kommatecken för nästa kolumn så här: {2,45; 3,83; 4,23; 5,74; 6,4; 7,16}.
- Vi kan sedan använda resultatet från den första delen av formeln i en VLOOKUP för att hitta motsvarande värde från vår VÄLJ och RAD -array.
Huvudkänslig VLOOKUP i Google Kalkylark
Använd den här metoden för att utföra en skiftlägeskänslig sökning i Google Kalkylark:
