stupid vlookup/ match/ index not working

[thesproing]

Hi guys,

please can anyone help with these formua. I simply trying to get the sum off all the numbers between two coloum references, but the starting colum varies depending on what row it is in.

The rows are represented by peoples names, the colums by months.

first formula tried:

=SUM(INDEX(Data!B5:Data!M23,1,MATCH("sep",Data!B3:M3)),(INDEX(Data!B5:Data!M23,1,(MATCH("sep",Data!B3:Z3))+11)))

For simplification i have labeled the row as 1 as i was having trouble with using text. The + 11 is how many cells to the right i would like the sum to include.

I also tried the VLOOKUP function, but its not even finding the cell i want it to, any ideas whats wrong with this....

=VLOOKUP("melanie",Data!A5:M23,MATCH("may",Data!A3:M3,0))

thanks for taking the time to look!

Olly

 

[Lewiy]

Can you possibly post an example of your worksheet with expected results as it’s kinda hard to understand what you are trying to do.

 

[Domenic]

Assuming that you're looking for an exact match when looking for the text value "sep", try...

=SUM(INDEX(Data!B5:M23,1,MATCH("sep",Data!B3:M3,0)):INDEX(Data!B5:M23,1,MATCH("sep",Data!B3:M3,0)+11))

Hope this helps!

Edit: Corrected typo - should have been a colon between the two INDEX functions, not a comma.

 

[thesproing]

ok guys, here are the tables

this is the first one linking employees to their start date;

http://s188.photobucket.com/albums/z303/thesproing/?action=view&current=Employeetable.jpg

and this is the second one, which has the number of hours each month cross referenced with the employee names and the months of the year.

http://s188.photobucket.com/albums/z303/thesproing/?action=view&current=figurestable.jpg

What i want to do is to add up all of the hours for each employee for the period of one year, starting at the month they began their employment, as shown on the first table.

i have to use one formulae as staff are variables, and tend to come and go.

Thanks again, hope this clears things up.

 

[Domenic]

Sorry, there was a typo in my formula. I've edited my previous post to correct it. However, it doesn't take into account the year or employee. Try the following instead...

=SUM(INDEX($B$5:$Y$23,MATCH("Melanie Davis",$A$5:$A$23,0),MATCH(2005,$B$1:$Y$1,0)+MATCH("Sep",$B$3:$M$3,0)-1):INDEX($B$5:$Y$23,MATCH("Melanie Davis",$A$5:$A$23,0),MATCH(2005,$B$1:$Y$1,0)+MATCH("Sep",$B$3:$M$3,0)+10))

Add the appropirate sheet reference, and adjust the ranges accordingly.

Hope this helps!

 

[thesproing]

thats amazing, thanks a bunch!!