Generisk formel
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Sammendrag
Skift mellom store og små bokstaver i Excel
Som standard er standardoppslag i Excel ikke store og små bokstaver. Både VLOOKUP og INDEX / MATCH vil ganske enkelt returnere den første kampen, og ignorerer saken.
En direkte måte å løse denne begrensningen på er å bruke en matriseformel basert på INDEX / MATCH med EXACT. Men hvis du bare leter etter numeriske verdier, gir SUMPRODUCT + EXACT også en interessant og fleksibel måte å gjøre et saksfølsomt oppslag på.
I eksemplet bruker vi følgende formel
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Selv om denne formelen er en matriseformel, trenger den ikke å angis med Control + Shift + Enter, siden SUMPRODUCT håndterer matriser naturlig.
Forklaring
SUMPRODUCT er designet for å fungere med matriser, som den multipliserer, deretter summerer.
I dette tilfellet er vi to matriser med SUMPRODUCT: B3: B8 og C3: C8. Trikset er å kjøre en test på verdiene i kolonne B, og deretter konvertere de resulterende SANNE / FALSE verdiene til 1 og 0. Vi kjører testen med EXAKT slik:
EXACT(E3,B3:B8)
Som produserer denne matrisen:
(FALSK; FALSK; SANT; FALSK; FALSK; FALSK)
Merk at den virkelige verdien i posisjon 3 er vår kamp. Deretter bruker vi det dobbelte negative (dvs. - som teknisk sett er en "dobbel unary") for å tvinge disse SANTE / FALSE verdiene til 1 og 0. Resultatet er denne matrisen:
(0; 0; 1; 0; 0; 0)
På dette tidspunktet i beregningen ser SUMPRODUCT-formelen slik ut:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
SUMPRODUCT multipliserer deretter bare elementene i hver matrise sammen for å produsere en endelig matrise:
(0; 0; 775; 0; 0; 0)
Hvilket SUMPRODUCT deretter summerer, og returnerer 775.
Så, kjernen i denne formelen er at FALSE-verdiene brukes til å avbryte alle andre verdier. De eneste verdiene som overlever er de som var SANNE.
Merk at fordi vi bruker SUMPRODUCT, kommer denne formelen med en unik vri: hvis det er flere treff, vil SUMPRODUCT returnere summen av disse kampene. Dette er kanskje ikke det du vil, så vær forsiktig hvis du forventer flere kamper!
Husk at denne formelen bare fungerer for numeriske verdier, fordi SUMPRODUCT ikke håndterer tekst. Hvis du vil hente tekst, kan du bruke INDEX / MATCH + EXACT.
