Two questions on how to make my formulae smarter

**fraserk** · 09-07-2009, 02:27 PM

Hi there,

I was wondering how to make my formulae a bit smarter. I am attaching a worksheet with sample data and a sample table that I would like to building. The table is on the second sheet, and this is where my question is centered. I have highlighted two cells on the second sheet and added comments to address the specific question. Cell A3 contains the formula I have created so far that I would like to make 'smarter'.

First, I would like the formula in C3 to be able to go to the first sheet and search across until it finds the appropriate building name, and then search in the appropriate fields, driven by this name, to return the data.

Secondly, I would love to be able to tell my formula that when I move over one cell in the data sheet, I want to move my calculation over three cells in the data sheet. I assume this would involve an hlookup, however defining the array based off of this lookup is where I am helpless. Currently my formula likes to move over 1 cell in the data sheet when I move over 1 field in the data table sheet.

These requests are redundant, meaning that either solution would retrieve the same data and I would be happy with either solution. While the first option is more complex it is also more fail safe; if I were to download the data again in a different order it would still be able to return the right data.

Out of general interest in increasing my excel knowledge I would LOVE some discussion on both, or a link to some good resources. Thanks for all the help!

Fraser

**daddylonglegs** · 09-07-2009, 02:38 PM

Hello Fraser,

There's more than one way to do this but possibly the most efficient way is to use INDEX and MATCH to retrieve the correct columns within SUMIF, i.e. use this formula in B3 copied across and down

=SUMIF(INDEX('Electical Data'!$A$2:$R$1000,0,MATCH(B$1,'Electical Data'!$A$1:$R$1,0)+1),$A3,INDEX('Electical Data'!$A$2:$R$1000,0,MATCH(B$1,'Electical Data'!$A$1:$R$1,0)+2))

**fraserk** · 09-07-2009, 04:38 PM

Great, thanks for the reply. I am going to look into the index and match functions and figure out what they do. I will give this formula a try in the sheet. Know of any better resources than Microsoft Excel Help...?