Returning an array specific to an element of a separate array

**peterszabla** · 10-19-2012, 07:00 PM

Hello,

I'm new to the forum and this is my first post. I took a look through, but could not find what I'm looking for so I would appreciate any help with what I'm trying to do.

I'm using the WORKDAY fuction, and the problem I'm running into is when passing in the array of holidays. The issue is that the holidays will vary depending on who the employee is, as I need to include vacation time, so I do not know which list of holidays to pass in before an employee is assigned and WORKDAY is executed.

I have created a hidden sheet that contains the employees in column A, and their days off in individual cells in the corresponding row. So it looks something like this:

A B C D
John Smith 41211 41212
Mike Jones 41211 41212 41213
Adam Smith 41216

So, what I need to do is to do is search through the column containing the list of employees and return the array of their holidays. For example if Mike Jones is the employee, then I would need the resulting array to be B2:B4. I've tried using various functions, but I have not been able to make this work. I thought I could make it work by using MATCH to return the row number, but I have not been able to successfully work that into my WORKDAY holidays argument. Any help, or pointing me in the right direction would work. I'm not opposed to changing around the layout of the hidden sheet if there is an easier way to do this.

**ConneXionLost** · 10-19-2012, 07:18 PM

How to attach a sample Excel workbook:

1. Below the "Quick Reply" thread frame, click the "Go Advanced" button.
2. On the edit toolbar, click the "Attachments" button.
3. In the "File Upload Manager" window, click the "Add Files" button.
4. In the "Upload Files from your Computer" window, click the "Select Files" button.
5. In the Windows Explorer window, locate and select your workbook, then click the "Open" button.
6. In the "Upload Files from your Computer" window, click the "Upload Files" button.
7. In the "File Upload Manager" window, click the "Done" button (lower right of screen).

**FDibbins** · 10-19-2012, 08:04 PM

it would be easier to offer suggestions if we could see what your workbook looks like?

however, maybe you can try this, assuming your data starts in A1...

=COUNT(OFFSET(A1,MATCH(A14,A1:A3,0)-1,1,MATCH(A14,A1:A3,0)-1,30))

you could use this as the basis to calc how many holiday days the employee has?

**peterszabla** · 10-20-2012, 01:36 PM

Thanks for the quick reply FDibbins. I'm currently out of town, but I will be back in the office on Monday and I will give that formula a shot. The reason I have not attached the sheet is that it is a very large project management document with numerous sheets and formulas in it that I've been working on for a while. If I cannot get it working when I'm back in the office, I'll create a sample sheet and attach it here.

Once again, appreciate the help.

**peterszabla** · 10-24-2012, 05:22 PM

Fdibbins, thank you for the help. I was able to do exactly what I need using a combination of OFFSET and MATCH, much like in your example. I doubt I would have stumbled across OFFSET on my own due to its unusual name. My issue has been resolved, I appreciate the help.

**FDibbins** · 10-24-2012, 05:30 PM

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neet and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)