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).

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).

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).