VLOOKUP IF formula help to report summary of tasks

[Kelly9678]

Hi Everyone! This is my first time posting as I just could not find the right formula to do what I need. SAMPLE FILE ATTACHED.

I am looking to have a spreadsheet in which a supervisor can list all of the various tasks for his team on one sheet ("Master List"). Then, each team member has a sheet that pulls only the tasks from the Master List that apply to that specific employee.

I tried several formulas and the closest I got was with a =IFERROR(INDEX... formula but it was only returning some of the tasks from the Master List.

Thanks in advance for your help!!

-K

[63falcondude]

Hello and welcome to the forum.

I would format the data in the 'Master List' worksheet as a table and then use this in A5 of the Bill worksheet:

=IFERROR(INDEX('Master List'!A:A,SMALL(IF(Table1[EMPLOYEE]="Bill",ROW(Table1[EMPLOYEE])),ROWS($1:1))),"") Ctrl Shift Enter

Drag the formula to the right and down as far as needed.
Since you don't need column C, you will have to adjust the INDEX portion to the column that you want to pull from (only need to do this for cells C5 and D5).

Do this for each sheet only changing "Bill" to the name required.

[kvsrinivasamurthy]

In A5 then dragged across for both the sheets.

Please Login or Register  to view this content.

[Kelly9678]

The supervisor has requested this be in Google Sheets vs Excel. Is this solution still applicable? I cannot figure it out with the pivot table.

[63falcondude]

Open up your working Excel sheet in Google Sheets and the formulas will be automatically converted for you.

[Kelly9678]

Excellent! I will try right now - thank you!

[Kelly9678]

The formula works! Thank you so much

My (hopefully) last question is now my workbook has a "Table1" sheet and a "Master List" sheet... Is the formula pulling from both sheets? I tried deleting one and the formula didn't work correctly. I tested them by changing the info slightly between the two sheets and seems like it is pulling from both. For example,

- "Table1" has Bill assigned to every task with "High" priority
- "Master List" has Kelly assigned to every task with "Low" priority

The "Bill" sheet is pulling Bill's tasks from "Table1" but pulling priority from "Master List". New SAMPLE FILE attached.

[63falcondude]

The table in the 'Table1' worksheet is called "Table1". The table in the 'Master List' worksheet is called "Table2".

If you want to refer to Table2, the table in the 'Master List' worksheet, simply change the formula references from Table1 to Table2 then confrim the formula again using Ctrl Shift Enter.

Since Bill is not in Table2, the cells in the 'Bill' worksheet will be blank.