Cell Reference

[Guest]

I have identified two cells in a table using vlookup & hlookup. I want to
sum the cells referred to by the lookups.

 

[Nick Hodge]

Hydro1guy

Just add the two lookups together

=VLOOKUP(Your_Vlookup)+HLOOKUP(Your-Hlookup)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Guest]

THat works but I want to sum the range of cells between the two. I think I
may have to use address but cannot get it to work. How else can I identify
the actual cell address for my range?

 

[Peo Sjoblom]

If you are summing a range your best bet is to use INDEX but you need to
indentify the 2 cells (no need for ADDRESS really) maybe using MATCH

 

[Guest]

1 2 3 4 5 6
01-Jun 31 32 33 34 35 36
02-Jun 7 8 9 10 11 12
03-Jun 13 14 15 16 17 18
04-Jun 19 20 21 22 23 24
05-Jun 25 26 27 28 29 30

this is a sample table. The top row is hour and the first colum is date. I
want to sum A range of cells determined by state date/hour and end date
hour.I can find the cells to start and finish the range by using V&H lookup.
but cannot get the formula to sum them to work.

=sum((INDEX(B2:H6,VLOOKUP(B12,B2:H6,(B13+1)),HLOOKUP(B18,B2:H6,(B17+1)))))

help would be greatly appreciated

 

[Peo Sjoblom]

OK, using your example, assume start date 3-June, end date 5-Jun
start hour 2 and end hour 5, using your example that would sum to 258,
with starts dat in B12, end in B13, start time in B17 and end in B18

=SUM(INDEX(B1:H6,MATCH(B12,B1:B6,0),MATCH(B17,B1:H1,0)):INDEX(B1:H6,MATCH(B1
3,B1:B6,0),MATCH(B18,B1:H1,0)))