Match Multiple Values
 

I have 2 variable values in spreadsheet 1 that match 2 values in spreadsheet
2. The B/MarkCost needs to be reported back to Spreadsheet 1. Any ideas on
how to do it?

Eg.

Spreadsheet 1

To Suburb Serv B/markCost
ALBION E
ALDERL N
ALBION N


Spreadsheet 2

TO SERV B/MarkCOST
ALBION E $37.06
ALDERL N $14.96
ALEXAN E $31.35
A.OVER G $62.70
ALBION N $7.80
ALBION A $78.40

Match Multiple Values
 

In Sheet1,
Put this in C2, normal ENTER:
=INDEX(Sheet2!C$2:C$7,MATCH(1,INDEX((Sheet2!A$2:A$ 7=A2)*(Sheet2!B$2:B$7=B2),),0))
Copy C2 down. Adapt the ranges to suit. This expression is generic, it'll
work even if Sheet2!C$2:C$7 were to contain text/mixed data to be returned,
which sumproduct cannot handle.

Celebrate your success, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Cazulu" wrote:
I have 2 variable values in spreadsheet 1 that match 2 values in spreadsheet
2. The B/MarkCost needs to be reported back to Spreadsheet 1. Any ideas on
how to do it?

Eg.

Spreadsheet 1

To Suburb Serv B/markCost
ALBION E
ALDERL N
ALBION N


Spreadsheet 2

TO SERV B/MarkCOST
ALBION E $37.06
ALDERL N $14.96
ALEXAN E $31.35
A.OVER G $62.70
ALBION N $7.80
ALBION A $78.40

Match Multiple Values
 

Try this:

=SUMPRODUCT(--(Sheet2!A$2:A$7=A2),--(Sheet2!B$2:B$7=B2),Sheet2!C$2:C$7)

--
Biff
Microsoft Excel MVP


"Cazulu" wrote in message
...
I have 2 variable values in spreadsheet 1 that match 2 values in
spreadsheet
2. The B/MarkCost needs to be reported back to Spreadsheet 1. Any ideas on
how to do it?

Eg.

Spreadsheet 1

To Suburb Serv B/markCost
ALBION E
ALDERL N
ALBION N


Spreadsheet 2

TO SERV B/MarkCOST
ALBION E $37.06
ALDERL N $14.96
ALEXAN E $31.35
A.OVER G $62.70
ALBION N $7.80
ALBION A $78.40

Match Multiple Values
 

Thanks for your help Max and T.V

"Cazulu" wrote:

I have 2 variable values in spreadsheet 1 that match 2 values in spreadsheet
2. The B/MarkCost needs to be reported back to Spreadsheet 1. Any ideas on
how to do it?

Eg.

Spreadsheet 1

To Suburb Serv B/markCost
ALBION E
ALDERL N
ALBION N


Spreadsheet 2

TO SERV B/MarkCOST
ALBION E $37.06
ALDERL N $14.96
ALEXAN E $31.35
A.OVER G $62.70
ALBION N $7.80
ALBION A $78.40