compare a few values

[tkaplan]

i have two worksheets:
sponsor and agency
agency sheet:
colA--------colB-------------colC---------
Period------week------------brand--------
1-----------1----------------CT----------
1-----------1----------------TT----------
1-----------2----------------CT----------

sponsor sheet:
colA--------colB-------------colC---------colD
Period------week------------brand--------amount
1-----------1----------------CT----------500
1-----------1----------------TT----------287
1-----------2----------------CT----------473

i need colD in agency to populate with the corresponding record - by period week and brand in colD of sponsor sheet.
I can't put an address because they may not be in the same order.
the agency sheet has more records than the sponsor does but they would always be sorted individually by period then by week then by brand.

[Domenic]

On your 'Agency' sheet...

D2, copied down:

=INDEX(Sponsor!$D$2:$D$4,MATCH(1,(Sponsor!$A$2:$A$4=Agency!A2)*(Sponsor!$B$2:$B$4=Agency!B2)*(Sponsor!$C$2:$C$4=Agency!C2),0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

[tkaplan]

thank you for the response.
question:
what is the difference between ctrl+shift+enter and just enter?

[Domenic]

thank you for the response.
question:
what is the difference between ctrl+shift+enter and just enter?

Confirming a formula with 'CONTROL+SHIFT+ENTER' tells Excel that you've entered an array formula. Have a look at the help menu under 'Array Formula' for more information.