# lookup a column value, averageif

#### Fraserkitchell

##### New Member
Hi there,

I have this function which is used to take a weekend and weekday average of 15 minute electric interval data for my building:
=4*(AVERAGE(IF(WEEKDAY('Electric DATA'!\$K\$2:\$K\$2975,2)> 5, IF(ROUND(MOD('Electric DATA'!\$K\$2:\$K\$2975,1)*1440,2)=D22,'Electric DATA'!\$L\$2:\$L\$2975))))

The only problem is that the WEEKDAY and ROUND(MOD( functions are hardcoded to columns. If I add more buildings, the sheet becomes unstable.

Anyone know how to have this formula look up a value in a cell, use that value to go to that column in an array of data (the value will be on top of the column), and perform this same averageif function on that column? it would help immensely...

Thanks!

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Fraserkitchell

##### New Member
I suppose I should add an example:

so if I want to have a cell storing the value "Archives Building",
I want this function to go to another sheet full of many columns, search a 1XMany array to find Archives building, and then count many lines down from there, averaging any data from a weekday or weekend.

It almost seems like an index match but I wanted to avoid a CSE function if it isnt too complicated. I have a fear that a CSE function will take up too many resources. Not sure if that is fair or not, but I'm going with it.

Thanks
Fraser

#### T. Valko

##### Well-known Member
I suppose I should add an example:

so if I want to have a cell storing the value "Archives Building",
I want this function to go to another sheet full of many columns, search a 1XMany array to find Archives building, and then count many lines down from there, averaging any data from a weekday or weekend.

It almost seems like an index match but I wanted to avoid a CSE function if it isnt too complicated. I have a fear that a CSE function will take up too many resources. Not sure if that is fair or not, but I'm going with it.

Thanks
Fraser
Are you trying to say that the reference to column K is a variable reference?

#### Fraserkitchell

##### New Member
Thats correct. The reference to K is variable, but above K will be a cell that I want to look up. L is also variable, but in this case the columb I need will always be one column right of the lookup column.

#### T. Valko

##### Well-known Member
Thats correct. The reference to K is variable, but above K will be a cell that I want to look up. L is also variable, but in this case the columb I need will always be one column right of the lookup column.
Ok, this is a typical approach to what you're describing.

Let's assume this is your table in the range A1:D7

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=256 border=0><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17></TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>Header1</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>Header2</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>Header3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>this</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">29 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">30 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">99 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>this</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">31 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">22 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">44 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>that</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">46 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">56 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">33 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>this</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">61 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">51 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">83 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>that</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">21 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">44 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">23 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>that</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">33 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">39 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">77 </TD></TR></TBODY></TABLE>

You want the average of "this" from the column with the column header of "header2".

This array entered** version will work in any version of Excel:

Better to use cells to hold the criteria:

• F2 = this
=AVERAGE(IF(A2:A7=F2,INDEX(B2:D7,,MATCH(G2,B1:D1,0))))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

This normally entered version will work in Excel 2007 and later:

=AVERAGEIF(A2:A7,F2,INDEX(B2:D7,,MATCH(G2,B1:D1,0)))

#### Fraserkitchell

##### New Member
ok! This will help. Columns are not defined so I'll assume the array function will then be performed on anything within that column. Thanks and I'll give it a try.

Fraser

#### Fraserkitchell

##### New Member
Ok I tried the above and I can't get this part to work.

(AVERAGE(IF(WEEKDAY(INDEX(DATA!A1:CP2689,,MATCH(Archives!
A1,DATA!A1:CP1,0)+1)),2)<6

This is the part that asks if a timestamp in a column is a weekday or a weekend day.

My function is breaking down here. Anyone able to sort through this mess? I'm working on it over here as well, will post if I get it working.

Fraser

#### Fraserkitchell

##### New Member
Got it.

=4*(AVERAGE(IF(WEEKDAY(INDEX(DATA!\$A\$2:\$CP\$2689,,MATCH(Archives!\$A\$1,DATA!\$A\$1:\$CP\$1,0))),2)<6,IF(ROUND(MOD(INDEX(DATA!\$A\$2:\$CP\$2689,,MATCH(Archives!\$A\$1,DATA!\$A\$1:\$CP\$1,0)),1)*1440,2)=D2,INDEX(DATA!\$A\$2:\$CP\$2689,,MATCH(Archives!\$A\$1,DATA!\$A\$1:\$CP\$1,0)+1))))

CSE Function.

Thanks for all the help!

Fraser

#### T. Valko

##### Well-known Member
Got it.

=4*(AVERAGE(IF(WEEKDAY(INDEX(DATA!\$A\$2:\$CP\$2689,,MATCH(Archives!\$A\$1,DATA!\$A\$1:\$CP\$1,0))),2)<6,IF(ROUND(MOD(INDEX(DATA!\$A\$2:\$CP\$2689,,MATCH(Archives!\$A\$1,DATA!\$A\$1:\$CP\$1,0)),1)*1440,2)=D2,INDEX(DATA!\$A\$2:\$CP\$2689,,MATCH(Archives!\$A\$1,DATA!\$A\$1:\$CP\$1,0)+1))))

CSE Function.

Thanks for all the help!

Fraser
Good deal. Thanks for the feedback!

Replies
4
Views
367
Replies
10
Views
356
Replies
2
Views
669
Replies
1
Views
121
Replies
5
Views
270

1,191,170
Messages
5,985,063
Members
439,938
Latest member
MAlhash

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back