lookup/match mult values

[Keep It Simple Stupid]

SHEET 1: (lookup Values)
COL A - House #
COL B - Group Name

SHEET 2: (lookup Vectors & Array)
COL C - House #
COL D - Group Name
COL E - Amount (RETURN VALUE)

I want to use a lookup formula of some sort to match the row from Sheet 1
using information from Columns A&B.
Then match it with the rows in Sheet 2 that have the EXACT matching
information in Columns C&D, and then return the value from Column E.

For example,
Sheet 1:
COL A COL B
1486 Yellow2

Sheet 2:
COL C COL D COL E
1486 Blue1 97
1486 Yellow2 26


So I would like the return value to be 26 (not 97)

 

[T. Valko]

Assuming each combination is unique:

=SUMPRODUCT(--(Sheet2!C1:C10=A1),--(Sheet2!D110=B1),Sheet2!E1:E10)

 

[Keep It Simple Stupid]

Does this account for alpha numeric values? I couldn't get it to work - I
tried using Value & Text in place of the dashes...
Am I overlooking something obvious?

 

[T. Valko]

Does this account for alpha numeric values?

No. There were no alpha numeric values in your posted sample.

Try this array formula** :

=INDEX(Sheet2!E1:E10,MATCH(1,(Sheet2!C1:C10=A1)*(Sheet2!D110=B1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)