Generisk formel
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Resumé
Skift mellem store og små bogstaver i Excel
Som standard er standardopslag i Excel ikke store og små bogstaver. Både VLOOKUP og INDEX / MATCH returnerer simpelthen den første kamp og ignorerer sagen.
En direkte måde at løse denne begrænsning på er at bruge en matrixformel baseret på INDEX / MATCH med EXACT. Men hvis du kun ser på numeriske værdier, giver SUMPRODUCT + EXACT også en interessant og fleksibel måde at foretage en sagsfølsom opslag på.
I eksemplet bruger vi følgende formel
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Selvom denne formel er en matrixformel, behøver den ikke at indtastes med Control + Shift + Enter, da SUMPRODUCT håndterer arrays indbygget.
Forklaring
SUMPRODUCT er designet til at arbejde med arrays, som det multiplicerer og derefter summerer.
I dette tilfælde er vi to arrays med SUMPRODUCT: B3: B8 og C3: C8. Tricket er at køre en test på værdierne i kolonne B og derefter konvertere de resulterende SAND / FALSKE værdier til 1 og 0. Vi kører testen med EXACT som sådan:
EXACT(E3,B3:B8)
Hvilket producerer dette array:
(FALSK; FALSK; SAND; FALSK; FALSK; FALSK)
Bemærk, at den sande værdi i position 3 er vores match. Derefter bruger vi det dobbelte negative (dvs. - som teknisk set er en "dobbelt unary") til at tvinge disse SAND / FALSKE værdier til 1 og 0. Resultatet er dette array:
(0; 0; 1; 0; 0; 0)
På dette tidspunkt i beregningen ser SUMPRODUCT-formlen sådan ud:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
SUMPRODUCT multiplicerer derefter blot elementerne i hver matrix sammen for at producere en endelig matrix:
(0; 0; 775; 0; 0; 0)
Hvilket SUMPRODUCT derefter summerer og returnerer 775.
Så kernen i denne formel er, at FALSE-værdierne bruges til at annullere alle andre værdier. De eneste værdier, der overlever, er de, der var SANDE.
Bemærk, at fordi vi bruger SUMPRODUCT, kommer denne formel med et unikt twist: hvis der er flere matches, returnerer SUMPRODUCT summen af disse matches. Dette kan måske ikke være, hvad du vil, så pas på, hvis du forventer flere kampe!
Husk, denne formel fungerer kun for numeriske værdier, fordi SUMPRODUCT ikke håndterer tekst. Hvis du vil hente tekst, skal du bruge INDEX / MATCH + EXACT.
