Help with cross tab Look up

bark01

Board Regular
Joined
Sep 6, 2006
Messages
118
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)
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

R_Stephens

Board Regular
Joined
Dec 23, 2004
Messages
143
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
 

Forum statistics

Threads
1,137,063
Messages
5,679,399
Members
419,825
Latest member
MegastarMagus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top