vlookup or pivot or match/index ??

Hi. I'm struggling to get a tool to work to do something and i wonder if someone could please quickly point me in the right direction.

My data are as follows
bank year revenue assets
X 2003 30000 450000
X 2004 34000 450056
Y 1999 3434 4493
X 2003 30000 450000

(Basically, a lot of repetition. For instance, row 1 = row 4)

I would like to create (pivot?) tables for revenue and assets
where banks are in the row and years are in the columns. Ultimate, I just want to have the data and format so that for a given bank and year, I can automate getting the rev or assets.

I can't seem to get this to work (it has been a while since I did something like this in excel. i'm ready to switch over to sas, but for various reasons, would like to do it in excel, if possible).

Thanks a lot.

Originally Posted by elbows

Hi. I'm struggling to get a tool to work to do something and i wonder if someone could please quickly point me in the right direction.

My data are as follows
bank year revenue assets
X 2003 30000 450000
X 2004 34000 450056
Y 1999 3434 4493
X 2003 30000 450000

(Basically, a lot of repetition. For instance, row 1 = row 4)

I would like to create (pivot?) tables for revenue and assets
where banks are in the row and years are in the columns. Ultimate, I just want to have the data and format so that for a given bank and year, I can automate getting the rev or assets.

I can't seem to get this to work (it has been a while since I did something like this in excel. i'm ready to switch over to sas, but for various reasons, would like to do it in excel, if possible).

Thanks a lot.

Hi

Attached is a solution using both a Pivot Table and a SumProduct table.

Rgds