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)

Assuming each combination is unique:

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

--
Biff
Microsoft Excel MVP


"Keep It Simple Stupid" wrote
in message ...
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)

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" wrote:

Assuming each combination is unique:

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

--
Biff
Microsoft Excel MVP


"Keep It Simple Stupid" wrote
in message ...
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)

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)*(S heet2!D1:D10=B1),0))

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

--
Biff
Microsoft Excel MVP


"Keep It Simple Stupid" wrote
in message ...
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" wrote:

Assuming each combination is unique:

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

--
Biff
Microsoft Excel MVP


"Keep It Simple Stupid"
wrote
in message ...
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)