Lookup dates and values
Suan wrote:
Hi
I want to set up a formula that allows me to enter a date on spreadsheet
A(eg May-09) and in Spreadsheet A also have a month actual column for various
expenses(expense type & amounts)
In spread sheet B I have actual data by month for a whole year by various
expenditure types.
I would like to in spreadsheet A Actual column pick up the various expenses
for that particular month entered in Spreadsheet A (May-09) from spread sheet
B for that month. And also every time I change the month(in spreadsheet A) it
picks up the relevant months expenses from sheet B!!!
eg SheetA
Date: May-09
Actual
Training $600
Salaries $5000
Consultants$800
Materials $600
Sheet B
May-09 June-09 Jul-09
Training $600 $1000 $1400
Salaries $5000 $5500 $5500
Consultants$800 $1500 $1000
Materials $600 $1000 $800
=INDEX(SheetB!$B$2:$D$5,MATCH(SheetA!$A4,SheetB!$A $2:$A$5,0),MATCH(SheetA!$B$1,SheetB!$B$1:$D$1,0))
Assumes
-SheetA and SheetB are in the same workbook
-SheetB has labels in row 1 and column A, data in B2:D5
-SheetA "Date" value is in B1 and desired "Training" value is in B4.
Paste the formula in B4 and fill down.
|