HLookUp a range (2003)

Topic

New Reply

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

Reply | Quote

Replies

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)))

Reply | Quote

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

Reply | Quote

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:D is the entire fourth column, it is equivalent to D1:D65536.

Reply | Quote

Thanks again Hans

Reply | Quote

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

Reply | Quote