# stupid vlookup/ match/ index not working

#### thesproing

##### Board Regular
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

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### Lewiy

##### Well-known Member
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

##### MrExcel MVP
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

##### Board Regular
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

##### MrExcel MVP
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))

Hope this helps!

#### thesproing

##### Board Regular
thats amazing, thanks a bunch!!

Replies
6
Views
294
Replies
3
Views
208
Replies
1
Views
341
Replies
7
Views
2K
Replies
6
Views
385

1,190,919
Messages
5,983,585
Members
439,852
Latest member
balasat

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back