lookup/match mult values
 

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)

lookup/match mult values
 

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)

lookup/match mult values
 

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)

lookup/match mult values
 

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)