Results 1 to 6 of 6
Thread: HLookUp a range (2003)

20060411, 08:42 #1
 Join Date
 Jun 2002
 Location
 Nottingham, Nottinghamshire, United Kingdom
 Posts
 257
 Thanks
 0
 Thanked 0 Times in 0 Posts
HLookUp a range (2003)
Hello
I am trying to use HLookUp to find the sum of a sequence of figures. The spreadsheet has columns for each workdate with the total of all the transactions. There is a row with the week number shown on every Monday. The attachment shows this clearer than I can explain.
What I want to be able to do is to look up a given week number e.g. 15 find the total sales for that day and for the subsequent 4 days, i.e. the week's total sales. Is this possible? I thought of HLookUP but this only returns the contents of one cell.
David

20060411, 08:55 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: HLookUp a range (2003)
Say that you put the week number in B9. The following formula will return the corresponding weekly total:
=SUM(OFFSET(INDEX(1:1,MATCH(B9,1:1,0)),2,0,1,5))
MATCH(B9,1:1,0) returns the column number of the week number in row 1.
INDEX(1:1,MATCH(B9,1:1,0)) is the cell in row 1 containing the week number.
OFFSET(INDEX(1:1,MATCH(B9,1:1,0)),2,0,1,5) is the range starting 2 rows down and 0 columns to the right of that cell, with a height of 1 row and a width of 5 columns.
SUM(OFFSET(INDEX(1:1,MATCH(B9,1:1,0)),2,0,1,5)) totals the values in the range.
This will return #N/A if you enter a nonexisting week number. To suppress this error, you can add a test:
=IF(ISERROR(MATCH(B9,1:1,0)),"",SUM(OFFSET(INDEX(1 :1,MATCH(B9,1:1,0)),2,0,1,5)))

20060412, 09:12 #3
 Join Date
 Jun 2002
 Location
 Nottingham, Nottinghamshire, United Kingdom
 Posts
 257
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: HLookUp a range (2003)
Thanks Hans. Very impressive formula, well explained  I've never seen one so complicated, but it worked a treat.
For my information, what is the significance of the 1:1 part of the Match function? I found that I could achieve the same thing by substituting an actual range so I assume it is some sort of shortcut for the top row. I guess I will need to use an actual range because the formula will be used in a different sheet, e.g. OtherSheet!A1:ZZ1.
David

20060412, 09:17 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: HLookUp a range (2003)
1:1 is the entire first row of the sheet  it is equivalent to A1:IV1. (There is no column ZZ in Excel yet  that will come in Excel 2007). If it is on another sheet, you can use OtherSheet!1:1.
Similarly, D is the entire fourth column, it is equivalent to D165536.

20060412, 11:45 #5
 Join Date
 Jun 2002
 Location
 Nottingham, Nottinghamshire, United Kingdom
 Posts
 257
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: HLookUp a range (2003)
Thanks again Hans

20060413, 09:54 #6
 Join Date
 Jun 2002
 Location
 Nottingham, Nottinghamshire, United Kingdom
 Posts
 257
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: HLookUp a range (2003)
I might add, for the benefit of anyone else who reads this thread, that i've found that the reference 1:1 to mean the whole of the first row can also be made an absolute reference $1:$1 and or OtherSheet!$1:$1. Having the reference as absolute seems to be more useful than relative in the context I was using it.
David