Help with cross tab Look up

[bark01]

I might be going about his the wrong way, but this is what I'm trying to do

I have a front sheet that refrences information from a number of tabs, each tab has its own line on the summary sheet.

The information on the summary sheet is date specific and a date range on the summary sheet controls what information is shown.

what I want to do is as follows

In Cell A36 on the summary sheet I want a formula that will take the end date shown on the summary sheet cell C2 and look for it TAB A column B11:B41 when it finds the matching date I want it to return the value on the same line in column BO.

So say for instance C2 on the summary sheet is set at as 15 of august. I want the formula to look for the 15th of august on Tab A then go along the line relating to the 15th of august and return the value in column BO.

Can anyone help me with this ?

This is what I was working with but it returns a Value error if all figure in BO are 0 or if i put a figures in BO the number given on the summary sheet doesn't match the figures in BO ?

=VLOOKUP(C2,'TAB A'!B11:B41,'TAB A'!BO11)

 

[R_Stephens]

Hi bark01,

You are getting error messages because your Vlookup formula is incorrect.

=vlookup(Lookup value, Range of data, column within range to return, find exact match)

So for your query you would need to use

=Vlookup(C2,'TAB A'!B11:BO41,66,False).

While you are only looking at 30 or so rows and only have 1 lookup this should be fine. However if you need a number of these vlookups with 66 columns of data, your spreadsheet willpotentially become very large & slow.

Personally for a lookup across so many columns I would use the following.

=INDEX('TAB A'!BO11:BO41,MATCH(C2,'TAB A'!B11:B41,0))

This basically uses the Match to work out how many rows down your selected range that your lookup value is, then the Index uses that number to return the value that is that many rows down in the range selected.

Hope it helps

Richard

 

[bark01]

thanks worked a treat, glad you could understand me.