AVERAGE using LOOKUP, INDEX, or MATCH

[DoooWhat]

I have 2 separate sheets: "Analysis" and "RawData". I am using the
Analysis page for various formulas that interpret the RawData.

This is the basic layout of the RawData sheet:

A B C D
1 Acct 1 Acct 2 Acct 3
2 Day 1 balance balance balance
3 Day 2 balance balance balance
4 Day 3 balance balance balance

I am looking at bank account balances on specific days. There are
roughly 100 accounts. Since their positions are subject to change, I
want to use dynamic formulas to analyze the data.

My Analysis page will have the account numbers in Row 1, just as they
are on the RawData page. However, they are not necessarily in the
same order, and they are subject to moving around.

Assuming that "Acct 1" is in cell B1 on the Analysis page, I want to
reference that account number and pull the average of all days for
that account from the RawData page.

Since I know the location of Acct 1, I could very simply just use
AVERAGE('RawData'!$B:$B). However, I will not know the column of
every account.

Thanks for your help.

Kevin

 

[T. Valko]

Try this:

=AVERAGE(INDEX(B24,,MATCH(acct_num,B11,0)))

Biff

 

[DoooWhat]

I tried that but for some reason it didn't work. It seems like it
would, but the totals do not add up correctly. This is the exact
formula I typed into my Analysis page:

=AVERAGE(INDEX('RawData'!$B$2:$IV$65000,,MATCH(B$1,'RawData'!
$1:$1,0)))

B1 (on the Analysis page) contains the account number I want.

 

[DoooWhat]

Nevermind. I toyed around with it and got it to work. Thanks for the
quick and effective response!

Kevin

 

[T. Valko]

Using:

MATCH(B$1,RawData!$1:$1,0)

You're off by 1 column

Either:

MATCH(B$1,RawData!$1:$1,0)-1

Or:

MATCH(B$1,RawData!B$1:IV$1,0)

Thanks for the feedback!

Biff