SUMPRODUCT not returning TEXT value
Chuck,
If only one pair matches
=INDEX(All12!$K:$K,SUMPRODUCT((All12!$A$12:$A$1500 0=DATA!$A$1)*(All12!$Q$12:$Q$15000=DATA!$K$2)*ROW( K12:K15000))
Otherwise, you need to limit to one match - enter using Ctrl-Shift-Enter
=INDEX(All12!$K:$K,MIN(IF((All12!$A$12:$A$15000=DA TA!$A$1)*(All12!$Q$12:$Q$15000=DATA!$K$2),ROW(K12: K15000))))
HTH,
Bernie
MS Excel MVP
"CLR" wrote in message
...
Hi All....
All12! column K contains TEXT, like A+
This formula works fine if I use a different column than K which contains
numbers but will not return the A+ value from column K......returns #VALUE!
instead.
=SUMPRODUCT((All12!$A$12:$A$15000=DATA!$A$1)*(All1 2!$Q$12:$Q$15000=DATA!$K$2)*(All12!$K$12:$K$15000) )
Is there a way to return TEXT values from column K if values in A and Q
meet the criteria?
TIA
Vaya con Dios,
Chuck, CABGx3
|