Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 non-existing 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)))

  3. #3
    3 Star Lounger
    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  5. #5
    3 Star Lounger
    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

  6. #6
    3 Star Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •