Need a special VLOOKUP...(or anything else)

[shajueasow]

Hello,
I am having an excel sheet with dates in Column AD, Employee Names in Column AE, and task to be done by the employee in Column AF. On a given date there are more than one employee to do a specific task. So what should be done (other than using Data>Filter) to get the list of Employees and their tasks on a specific date. Like giving a date in Column C and getting the needed data..... Employees name in Column D, and the task in Column F.
VLOOKUP can return only one value. Any other way to do this...?

TIA.

 

[Mike LH]

Hi,

Try this ARRAY formula, see below for how to enter it. This looks at a date in C2. Put the formula in a cell and ARRAY enter it then drag right 1 column and then drag down as far as you want, it will return blanks when it runs out of matches..

=IFERROR(INDEX(AE$1:AE$100,SMALL(IF($AD$1:$AD$100=$C$2,ROW($AD$1:$AD$100)),ROW(1:1))),"")

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

 

[Aladin Akyurek]

Hello,
I am having an excel sheet with dates in Column AD, Employee Names in Column AE, and task to be done by the employee in Column AF. On a given date there are more than one employee to do a specific task. So what should be done (other than using Data>Filter) to get the list of Employees and their tasks on a specific date. Like giving a date in Column C and getting the needed data..... Employees name in Column D, and the task in Column F.
VLOOKUP can return only one value. Any other way to do this...?

TIA.

C1 houses a criterion date.

C2: Idx; D2: Employee; E2: Task

D1, just enter:

=COUNTIFS(AD:AD,C1)

C3, control+shift+enter, not just enter,and copy down:

=IF(ROWS($C$3:C3)<=$D$1,SMALL(IF($AD$2:$AD$1000=$C$1,
    ROW($AD$2:$AD$1000)-ROW($AD$2)+1),ROWS($C$3:C3)),"")

D3, just enter, copy across, and down:

=IF($C3="","",INDEX(AE$2:AE$1000,$C3))

 

[Aladin Akyurek]

[...]

=IFERROR(INDEX(AE$1:AE$100,SMALL(IF($AD$1:$AD$100=$C$2,ROW($AD$1:$AD$100)),ROW(1:1))),"")

[...]

Not robust and moreover, less efficient. See post #3 which should be satisfactory on these scores.

 

[shajueasow]

Thank You Mike LH and Aladin Akyurek for looking into this matter and sending in your solutions.
Aladin Akyurek's solution is a complete one.