Issue with Nested Vlookup

[suttonutd]

Hi everyone, this is really bugging me and it's probably something simple but i just can't get it.

Here is my raw data:

<TABLE style="WIDTH: 549pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=732 border=0><COLGROUP><COL style="WIDTH: 165pt; mso-width-source: userset; mso-width-alt: 8045" width=220><COL style="WIDTH: 48pt" span=8 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl106 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 165pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=220 height=17>Client</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>From</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>To</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>1</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>2</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>3</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>4</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>5</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl106 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl106 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Client 1</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Jan</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Dec</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Jan</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Feb</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Mar</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Apr</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">May</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Jun</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl106 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Client 2</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Jan</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Dec</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Jan</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Feb</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Mar</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Apr</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">May</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Jun</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl106 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Client 3</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Jan</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Dec</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Jan</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Feb</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Mar</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Apr</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">May</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Jun</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl106 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Client 4</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Apr</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Mar</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Apr</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">May</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Jun</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Jul</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Aug</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Sep</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl106 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Catalent</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Jan</TD><TD class=xl107 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Dec</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Jan</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Feb</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Mar</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Apr</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">May</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Jun</TD></TR></TBODY></TABLE>

On another sheet, I pull monthly data like this:

<TABLE style="WIDTH: 228pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=304 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 180pt; mso-width-source: userset; mso-width-alt: 8777" width=240><TBODY><TR style="HEIGHT: 51pt" height=68><TD class=xl106 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 51pt; BACKGROUND-COLOR: red" width=64 height=68>Tran Period Month - Year</TD><TD class=xl106 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 180pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: red" width=240>Client Group Name 2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl107 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>JAN-2011</TD><TD class=xl107 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent">Client1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl107 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>JAN-2011</TD><TD class=xl107 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent">Client2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl107 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>JAN-2011</TD><TD class=xl107 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent">Client3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl107 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>JAN-2011</TD><TD class=xl107 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent">Client4</TD></TR></TBODY></TABLE>

What I need to do, is find Client 1 on the raw data, then Find Jan and return it's respective Number.

So for example, Client 1 Jan is 1 but Client 4 Apr is 1

I have done a vlookup to find the client, also a sumif to find the number respective to Jan, but can't combine them or nest any formulae.

Here are my attempts:
=VLOOKUP(E3,'Contract Period (2)'!A5:P46,3,FALSE)
=SUMIF('Contract Period (2)'!D5:P5,LEFT(D4,3),'Contract Period (2)'!D3:P3)

NOTE: "Contract Period (2)" being the raw data.

Please advise, i'm head scratching!

Thanks for your help,
Steve

 

[Gerald Higgins]

I'm not completely clear what you want.
What is the result for Client 4 ? Is it 10 ?

 

[suttonutd]

Hi Gerald, that's exactly what it is, I just couldn't copy enough going across.

So under 10 of the raw data will be Jan for Client4.

Hoping you can help.
Thanks,
Steve

 

[Gerald Higgins]

Assuming the data table you posted is in the range A1:O7, with the word "Client" in A1, "Client 1" in A3, "From" in B1, the "Jan" for client 1 in B3, and so on, this returns 10 for client 4

=MATCH("Jan",OFFSET(A3,MATCH("Client 4",A3:A7,0)-1,3,1,15),0)

 

[suttonutd]

Hi Gerald, thanks for your help on this. The only issue I can see is that Offset Formula has fixed criteria but the Client Name could be different and the location of the month could be "Jan","Feb" etc and the location of that month within the data could be from 1 to 12. So it's not always 10 cells across.

Hope that makes sense and you can help.

THANKS
Steve

 

[Gerald Higgins]

Yes that makes sense.

First of all, the 10 is not part of the formula, it's the result.
Try just moving the word JAN for Client 4 to another column - the 10 should change to whatever column you put it into.

All the components of my formula can be controlled by formulas themselves.
For example, if you are specifying your client name in cell Z100, then your formula would be this

=MATCH("Jan",OFFSET(A3,MATCH(Z100,A3:A7,0)-1,3,1,15),0)

Then, if you don't always want to look at Jan, and sometimes want to specify a different month, let's say you specify the month in cell X99.
Your formula would be

=MATCH(X99,OFFSET(A3,MATCH(Z100,A3:A7,0)-1,3,1,15),0)

and so on . . .

 

[suttonutd]

Hi Gerald, thanks so much for your help on this, and I can really see the end in sight now!

Final issue, I have replicated the formula into my excel model and works okay... good news! BUT, the issue I have is that the Data is one worksheet, and I want the formula on another worksheet. When I start linking to this worksheet, the formula breaks down and returns a #N/A's !?!?

Would you know why this is?

Within the same worksheet:

<TABLE style="WIDTH: 300pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=400 border=0><COLGROUP><COL style="WIDTH: 300pt; mso-width-source: userset; mso-width-alt: 7314" width=400><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl106 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 300pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=400 height=20>=MATCH("Feb",OFFSET(A5,MATCH(A5,A5:A17,0)-1,3,1,15),0)


</TD></TR></TBODY></TABLE>
Linked to other worksheet:

<TABLE style="WIDTH: 518pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=690 border=0><COLGROUP><COL style="WIDTH: 518pt; mso-width-source: userset; mso-width-alt: 12617" width=690><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl106 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 518pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=690 height=20>=MATCH(LEFT('Data 2011 (2)'!D2,3),OFFSET('Data 2011 (2)'!E2,MATCH('Data 2011 (2)'!E2,A5:A17,0)-1,3,1,15),0)


</TD></TR></TBODY></TABLE>
Is it because some of the criteria have to be within that range of A5 to A17?

Thanks

 

[Gerald Higgins]

It's difficult to say.

What's in Data 2011 (2)!D2 ?
Is it a date ?

 

[suttonutd]

Gerald, It works!

I realised that I need to understand these formulae and once I got my head around them, it now works fine.

Thanks so much for your help, I can't tell you how much this has helped me with this excel model and for my learning curve. Only if I could send you a pint of the finest beer as a thank you!

Thanks

 

[Gerald Higgins]

You're welcome, thanks for coming back to say thanks, I'll have a Friday afternoon e-pint please...

cheers !