# Thread: Formula error Sumproduct , combined wth Index and match

1. ## Formula error Sumproduct , combined wth Index and match

I am trying to determine which employee/employees are on leave in the current month, but get an incorrect solution-see F6

2. I tried a slightly different approach and can get it to work with numbers but can't get it to work with letters; instead of an "a" character to indicate leave I have had to use a "1" (but it could be any number). Not sure if this will help you, but hope that it does!

3. Thanks for the help, much appreciated

1) having two commas ,, after .... November, December),,match
2) why + 2 after MATCH(A6,'Leave Register'!\$C\$2:\$D\$2)+2 ?
3) significance of - 3 in this section of formula MONTH(\$F\$1)-3))>

Code:
` =IF(ISERROR(MATCH(A6,'Leave Register'!\$C\$2:\$D\$2)),"Employee not in Leave Register",IF(MAX(INDEX((April,May,June,July,August,September,October,November,December),,MATCH(A6,'Leave Register'!\$C\$2:\$D\$2)+2,MONTH(\$F\$1)-3))>0,"Yes","No"))`

4. The two commas are because that's where you would put row number if you were looking for a specific row, but you just want to know if there's something anywhere in the column so it's left blank.
The +2 is because the names start in column 3, not column 1 on the second sheet.
The -3 is because April is the first month in the list, but is month 4.

5. Thanks for the info. I now fully understand the logic

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•