Excel formula, Lookup & return row1, column 12

[JonesyUK]

Hi - thanks for your help.

I have an Excel sheet which has data in columns C through O.
Column C is the column heading, colum D to N are the months (1 to 12) and column O is the "Total". Example:

............ColumnC...|.ColumnD.|.ColumnE.|.etc.
ROW5.....Location1|.Month1...|.Month2..|.etc..|TOTAL
ROW6..................|...200......|....300....|.etc..|500
ROW7.....Location2| Month1...|.Month2..|.etc..|TOTAL
ROW8..................|...100......|....300....| etc..|400


I would like to create summary as follows:
Location1 = 500
Location2 = 400
Location3, = x, etc.

I can't use vlookup because the location name is in the same row as the column headings, and I am not allowed to make any changes to the original file.

I can use index and offset for the first location, but ideally I need the formula to find "Location1", return the TOTAL which is 1 row BELOW the column heading ("TOTAL") and then to move on to Location2, etc.

Hope this makes sense...

Thanks,
Clint

 

[T. Valko]

Hi - thanks for your help.

I have an Excel sheet which has data in columns C through O.
Column C is the column heading, colum D to N are the months (1 to 12) and column O is the "Total". Example:

............ColumnC...|.ColumnD.|.ColumnE.|.etc.
ROW5.....Location1|.Month1...|.Month2..|.etc..|TOTAL
ROW6..................|...200......|....300....|.etc..|500
ROW7.....Location2| Month1...|.Month2..|.etc..|TOTAL
ROW8..................|...100......|....300....| etc..|400


I would like to create summary as follows:
Location1 = 500
Location2 = 400
Location3, = x, etc.

I can't use vlookup because the location name is in the same row as the column headings, and I am not allowed to make any changes to the original file.

I can use index and offset for the first location, but ideally I need the formula to find "Location1", return the TOTAL which is 1 row BELOW the column heading ("TOTAL") and then to move on to Location2, etc.

Hope this makes sense...

Thanks,
Clint

Try this...

A2 = some location

=SUM(INDEX(D5:N15,MATCH(A2,C5:C15,0)+1,0))

 

[JonesyUK]

perfect! thanks very much... genius...

 

[T. Valko]

perfect! thanks very much... genius...

You're welcome. Thanks for the feedback!