compare a few values

T

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 - b
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 woul
always be sorted individually by period then by week then by brand
 
D

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!
 
T

tkaplan

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

Domenic

tkaplan said:
thank you for the response.
question:
what is the difference between ctrl+shift+enter and just enter?
Click to expand...

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.
 
Top