Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick value at intersection of location & date (MMM-YY)
Hi,
Help me out.. thanks in advance. I need to pick a value from a table (budget) having 71 rows & 14 columns 1st row is the header like below: 1st column is Business location, 2nd column is Jan-06, 3rd column Feb-06..Dec-06 then Total. (here the date is 01-01-06, 01-02-06...formatted as Jan-06, Feb-06) Data starts from 2nd Row like: 2nd row of 1st column is Dubai, 2nd column is 500, 3rd column is 650, so on.. 3rd row of 1st column is Delhi, 2nd column is 700, 3rd column is 900, so on.. in another sheet I have at C2 Dubai and D2 date 03-03-2006, now at E2, I need formula to get the value from data sheet available at the intersection of Dubai row & Mar-06 column. Like wise at C3 Delhi, D3 05-03-2006, E3 = formula required (copy the above formula). |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick value at intersection of location & date (MMM-YY)
Hi Eddy
Try =INDEX(Sheet1!$A$1:$N$71,MATCH(Sheet2!$C2,Sheet1!$ A:$A,0),MATCH(Sheet2!$D2,Sheet!$1:$1)) You need to format cells in column D of Sheet 2 as mmm-yy the same as in row 1 of Sheet1. -- Regards Roger Govier "Eddy Stan" wrote in message ... Hi, Help me out.. thanks in advance. I need to pick a value from a table (budget) having 71 rows & 14 columns 1st row is the header like below: 1st column is Business location, 2nd column is Jan-06, 3rd column Feb-06..Dec-06 then Total. (here the date is 01-01-06, 01-02-06...formatted as Jan-06, Feb-06) Data starts from 2nd Row like: 2nd row of 1st column is Dubai, 2nd column is 500, 3rd column is 650, so on.. 3rd row of 1st column is Delhi, 2nd column is 700, 3rd column is 900, so on.. in another sheet I have at C2 Dubai and D2 date 03-03-2006, now at E2, I need formula to get the value from data sheet available at the intersection of Dubai row & Mar-06 column. Like wise at C3 Delhi, D3 05-03-2006, E3 = formula required (copy the above formula). |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick value at intersection of location & date (MMM-YY)
Thanks Mr Roger
the discussion board is very encouraging ! "Roger Govier" wrote: Hi Eddy Try =INDEX(Sheet1!$A$1:$N$71,MATCH(Sheet2!$C2,Sheet1!$ A:$A,0),MATCH(Sheet2!$D2,Sheet!$1:$1)) You need to format cells in column D of Sheet 2 as mmm-yy the same as in row 1 of Sheet1. -- Regards Roger Govier "Eddy Stan" wrote in message ... Hi, Help me out.. thanks in advance. I need to pick a value from a table (budget) having 71 rows & 14 columns 1st row is the header like below: 1st column is Business location, 2nd column is Jan-06, 3rd column Feb-06..Dec-06 then Total. (here the date is 01-01-06, 01-02-06...formatted as Jan-06, Feb-06) Data starts from 2nd Row like: 2nd row of 1st column is Dubai, 2nd column is 500, 3rd column is 650, so on.. 3rd row of 1st column is Delhi, 2nd column is 700, 3rd column is 900, so on.. in another sheet I have at C2 Dubai and D2 date 03-03-2006, now at E2, I need formula to get the value from data sheet available at the intersection of Dubai row & Mar-06 column. Like wise at C3 Delhi, D3 05-03-2006, E3 = formula required (copy the above formula). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
Date Format Problems?? | Excel Discussion (Misc queries) | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
Can a date be used for conditional formatting? | Excel Worksheet Functions | |||
date and time | New Users to Excel |