=SUM((ISNUMBER(MATCH(C2:C19,IF(B2:B7=F2,A2:A7),0)) )*(D2:D19))

as an array formula, Ctrl-Shift-Enter it

--
__________________________________
HTH

Bob

"Philh3325" wrote in message
...
I am trying to sum the data in the Data table as Regions (see Results
table),
using the Lookup table to find the region (Lookup Table), without the need
to
have a lookup column (XX) to Sumif on in the Data Table. Can anyone help
please.
See below.

Lookup Table Data Table Results Table
NHU Region NHU Data XX Region Solution
a aa a 1 aa aa 63
b bb a 2 aa bb 57
c aa a 3 aa cc 51
d aa b 4 bb dd
e bb b 5 bb ee
f cc b 6 bb etc
c 7 aa
c 8 aa
c 9 aa
d 10 aa
d 11 aa
d 12 aa
e 13 bb
e 14 bb
e 15 bb
f 16 cc
f 17 cc
f 18 cc