Vlookup on several columns

[christineswest]

I need to know if a particular customer number (1) received a particular contract type (2) with a certain effective date (3). Is there a way to do the vlookup formula on multiple columns?

 

[MrVillareal]

Hi please post your sample data and expected results.

 

[christineswest]

I don't want to give out the same contract type for the same customer for the same time period within 2011.

I have customer #2760 that already has contract types A, B, C and D in 2011. A and B started in January, C started in April and D started in July. A new (fifth) record comes through for customer #2760 giving him another contract type D in 2011. The new contract type D starts in October. I would accept this contract because it is for a different time period.

If the new contrct type D started in July, I would not accept it because it already has a type D that starts in July.

 

[adamthompson3232]

It sounds like you need an If(AND type statement but without the data I can't say for sure.

 

[christineswest]

The rules say I may not post an attachment. I have a sample file with this situation.

 

[Joe4]

You cannot attach files, but you can post screen images.
See here: http://www.mrexcel.com/forum/showthread.php?t=508133

 

[christineswest]

This is a small shot of my master file. I want to look new records up in the master file and see if there is the same type of contract for the same time period.

<TABLE style="WIDTH: 314pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=418 x:str><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; WIDTH: 65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #d4d0c8" class=xl24 height=17 width=86>Customer</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; WIDTH: 95pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #d4d0c8" class=xl25 width=127>Contract Type</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; WIDTH: 71pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #d4d0c8" class=xl26 width=94>Start Date</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl35 width=111>End Date</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 height=17>002760</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28>PI NBO - CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 x:num="40544">1/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl36 x:num="40633">3/31/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 height=17>002760</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28>PI PPA CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 x:num="40544">1/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl37> 12/31/2012</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 height=17>002760</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28>PI $50 BNB - CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 x:num="40634">4/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl36 x:num="40724">6/30/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30 height=17>002760</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28>PI $100 BNB - CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl31 x:num="40725">7/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl38 x:num="40816">9/30/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 height=17>002781</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28>PI FVB CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 x:num="40544">1/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl36 x:num="41274">12/31/2012</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 height=17>002781</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28>PI $100 BNB - CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 x:num="40634">4/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl36 x:num="40724">6/30/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30 height=17>002781</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28>PI $100 BNB - CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl31 x:num="40725">7/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl38 x:num="40816">9/30/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 height=17>000897</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28>PI $50 BNB - CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 x:num="40634">4/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl36 x:num="40724">6/30/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30 height=17>000897</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28>PI PPA CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl31 x:num="40544">1/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl38 x:num="41274">12/31/2012</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 height=17>001511</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28>PI NBO - CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 x:num="40544">1/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl36 x:num="40633">3/31/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 height=17>001511</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28>PI PPA CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 x:num="40544">1/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl36 x:num="41274">12/31/2012</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 height=17>000347</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28>PI FVB MA</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 x:num="40544">1/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl36 x:num="41274">12/31/2012</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 height=17>000394</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28>PI PPA CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 x:num="40544">1/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl36 x:num="41274">12/31/2012</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30 height=17>000442</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28>PI PPA CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl31 x:num="40544">1/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl38 x:num="41274">12/31/2012</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 height=17>000938</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28>PI FVB CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 x:num="40544">1/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl36 x:num="41274">12/31/2012</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl32 height=18>001108</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl33>PI FVB CW</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl34 x:num="40544">1/1/2011</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl39 x:num="41274">12/31/2012</TD></TR></TBODY></TABLE>


This is a small shot of the new records.

<TABLE style="WIDTH: 320pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=426 x:str><COLGROUP><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; WIDTH: 73pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #d4d0c8" class=xl24 height=17 width=97>Customer</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; WIDTH: 95pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #d4d0c8" class=xl25 width=127>Contract Type</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; WIDTH: 81pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #d4d0c8" class=xl26 width=108>Start Date</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl31 width=94>End Date</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28 height=17>0CKL85</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27>PI $50 BNB - CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 x:num="40787">9/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl32 x:num="40877">11/30/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28 height=17>0CTR67</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27>PI $50 BNB - CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 x:num="40787">9/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl32 x:num="40877">11/30/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28 height=17>002760</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27>PI $100 BNB - CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 x:num="40817">10/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl32 x:num="40908">12/31/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28 height=17>002781</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27>PI FVB CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 x:num="40544">1/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl32 x:num="41274">12/31/2012</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28 height=17>0CXW61</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27>PI NBO - CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 x:num="40787">9/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl32 x:num="40908">12/31/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28 height=17>0CXY26</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27>PI NBO - CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 x:num="40787">9/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl32 x:num="40908">12/31/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28 height=17>0CZQ85</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27>PI NBO - CW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 x:num="40787">9/1/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl32 x:num="40908">12/31/2011</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl33 height=18>0CZQ43</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30>PI NBO - CW</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl34 x:num="40787">9/1/2011</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl35 x:num="40908">12/31/2011</TD></TR></TBODY></TABLE>


Customer 2760 is acceptable but customer 2781 is not.

 

[adamthompson3232]

I would add a column to your master data sheet that concatenantes all the variables you are looking at. Such a formula might look like this in column E of the master data sheet:

=CONCATENATE(A1,B1,C1)

This gives you the customer name, contract type, and effective date all in one field. You can then copy this formula down for all records in your master data file.

Then in your new data file you can do the following in column E:

=IF(ISERROR(VLOOKUP(CONCATENATE(A1,B1,C1),MASTERFILE$D:$D,1,FALSE)),"yes","no"))

This will return a value of "yes" if the contract is acceptable and a value of "no" if it is not.

There may be an easier way but this is how I would do it.

 

[christineswest]

Thanks! I will try that.

 

[jasonb75]

Maybe

Excel Workbook
	A	B	C	D	E
1	Customer	Contract Type	Start Date	End Date
2	2760	PI NBO - CW	01/01/2011	3/31/2011
3	2760	PI PPA CW	01/01/2011	12/31/2012
4	2760	PI $50 BNB - CW	04/01/2011	6/30/2011
5	2760	PI $100 BNB - CW	07/01/2011	9/30/2011
6	2781	PI FVB CW	01/01/2011	12/31/2012
7	2781	PI $100 BNB - CW	04/01/2011	6/30/2011
8	2781	PI $100 BNB - CW	07/01/2011	9/30/2011
9	897	PI $50 BNB - CW	04/01/2011	6/30/2011
10	897	PI PPA CW	01/01/2011	12/31/2012
11	1511	PI NBO - CW	01/01/2011	3/31/2011
12	1511	PI PPA CW	01/01/2011	12/31/2012
13	347	PI FVB MA	01/01/2011	12/31/2012
14	394	PI PPA CW	01/01/2011	12/31/2012
15	442	PI PPA CW	01/01/2011	12/31/2012
16	938	PI FVB CW	01/01/2011	12/31/2012
17	1108	PI FVB CW	01/01/2011	12/31/2012
18
19	Customer	Contract Type	Start Date	End Date	Acceptable?
20	0CKL85	PI $50 BNB - CW	09/01/2011	11/30/2011	Yes
21	0CTR67	PI $50 BNB - CW	09/01/2011	11/30/2011	Yes
22	2760	PI $100 BNB - CW	10/01/2011	12/31/2011	No
23	2781	PI FVB CW	01/01/2011	12/31/2012	Yes
24	0CXW61	PI NBO - CW	09/01/2011	12/31/2011	Yes
25	0CXY26	PI NBO - CW	09/01/2011	12/31/2011	Yes
26	0CZQ85	PI NBO - CW	09/01/2011	12/31/2011	Yes
27	0CZQ43	PI NBO - CW	09/01/2011	12/31/2011	Yes
Sheet1