Generisk formel
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Sammanfattning
Skiftlägeskänsliga sökningar i Excel
Som standard är standarduppslag i Excel inte skiftlägeskänsliga. Både VLOOKUP och INDEX / MATCH returnerar helt enkelt den första matchen och ignorerar fallet.
Ett direkt sätt att lösa denna begränsning är att använda en matrisformel baserad på INDEX / MATCH med EXACT. Om du bara letar efter numeriska värden ger SUMPRODUCT + EXACT också ett intressant och flexibelt sätt att göra en skiftlägeskänslig uppslagning.
I exemplet använder vi följande formel
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Även om denna formel är en matrisformel behöver den inte anges med Control + Shift + Enter, eftersom SUMPRODUCT hanterar arrays naturligt.
Förklaring
SUMPRODUCT är utformad för att fungera med matriser, som den multiplicerar och sedan summerar.
I det här fallet är vi två matriser med SUMPRODUCT: B3: B8 och C3: C8. Tricket är att köra ett test på värdena i kolumn B och sedan konvertera de resulterande SANT / FALSKA värdena till 1 och 0. Vi kör testet med EXAKT så här:
EXACT(E3,B3:B8)
Vilket producerar denna array:
(FALSK; FALSK; SANT; FALSK; FALSK; FALSK)
Observera att det verkliga värdet i position 3 är vår match. Sedan använder vi det dubbla negativet (dvs. - vilket tekniskt är en "dubbel unary") för att tvinga dessa SANT / FALSKA värden till 1 och 0. Resultatet är denna array:
(0; 0; 1; 0; 0; 0)
Vid denna punkt i beräkningen ser SUMPRODUCT-formeln ut så här:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
SUMPRODUCT multiplicerar sedan enkelt objekten i varje array tillsammans för att producera en slutlig array:
(0; 0; 775; 0; 0; 0)
Vilket SUMPRODUCT sedan summerar och returnerar 775.
Så kärnan i denna formel är att FALSE-värdena används för att ta bort alla andra värden. De enda värden som överlever är de som var SANNA.
Observera att eftersom vi använder SUMPRODUCT kommer denna formel med en unik twist: om det finns flera matchningar kommer SUMPRODUCT att returnera summan av dessa matchningar. Detta kanske eller inte är vad du vill, så var försiktig om du förväntar dig flera matcher!
Kom ihåg att den här formeln bara fungerar för numeriska värden, eftersom SUMPRODUCT inte hanterar text. Om du vill hämta text använder du INDEX / MATCH + EXAKT.
