Index Function

[baker_89]

I have a table that lists names and dates in the rows next to each of them. Names in B3:B105 (range named "Name") and the Dates in C3:L105(range named "Requests") the entire range is B3:L105 (named "DaysOff").

I want to be able to enter a date (in cell N5) and have any name(s) be returned that matches the date.
(if possible I'd also like to be able to enter a range of dates and have the names be returned, but one at a time would suffice for now)

This is what i have so far but it keeps coming up blank.

=IF(ISERROR(INDEX(Requests,SMALL(IF(DaysOff=$N$5,ROW(Name)),ROW(3:105)),2)),"",INDEX(Requests,SMALL(IF(Name=$N$5,ROW(Name)),ROW(3:105)),2))

 

[konew1]

why not use autofilter on the date column. You can then filter for a single date or for a range of dates.
My suggestion is based on assumption that the date column for "DaysOff" has a single date in any cell not a text string for a date range (eg, in UK style date format, not 1/6/15-3/6/15)

 

[baker_89]

I suppose that would work actually but I am trying to make it simple for some co-workers who aren't the best on excel. This is for building our weekly employee schedule. When an employee requests the day off I want it to show who asked for the day off that occurs during the week that is being put together.

 

[AhoyNC]

See if this works for you.
These are array formulas and must be entered with CTRL-SHIFT-ENTER.

Change ranges to match your data (named ranges).

Formula in N7 is for a single date and formula in P7 for a date range.
One question - could you ever have a duplicate date in the same row (looking at row 3 for name1 below could the date of 6/1/15 show up 2 or more times in row 3)?

Excel Workbook
	A	B	C	D	E	F	L	M	N	O	P	Q
1
2		Names	Dates								Date
3		Name1	4/2/2015	6/1/2015		6/10/2015			Single		Range
4		Name2			5/1/2015				Date		5/31/2015
5		Name3							6/1/2015		6/9/2015
6		Name4	6/1/2015
7		Name5		6/6/2015	6/8/2015				Name1		Name1
8		Name6				6/1/2015			Name4		Name4
9									Name6		Name5
10											Name5
11											Name6
12
Sheet

 

[baker_89]

No the same date should never show up twice in the same row but it could in the same column, I entered this in and changed my ranges but the cell comes up showing the formula in all cells that were entered with the array, (formula text is showing).

 

[AhoyNC]

I noticed a problem with the formula for a range of dates. It returned name5 two times.
This formula (the one in P7 needs to be changed to (you will need to add your ISERROR).

=INDEX($B$3:$B$105,SMALL(IF(FREQUENCY(IF($C$3:$L$105>=$P$4,IF($C$3:$L$105<=$P$5,MATCH($B$3:$B$105,$B$3:$B$105,0))),ROW($B$3:$B$105)-ROW($B$3)+1),ROW($B$3:$B$105)-ROW($B$3)+1),ROWS($B$3:B3)))

This is an array formula that needs to be entered using CTRL-SHIFT-ENTER.

If the formula showed up as text.
Don't enter the {} around the formula. When you enter the formula with CTRL-SHIFT-ENTER Excel will put the {} around the formula for you if you do it right.

 

[baker_89]

When I enter these in, it shows just the same name multiple times (only 1 date occurrence for the name, and there are others within the date range), yes I forgot to enter the array properly that fixed that issue.

 

[AhoyNC]

Take a look at the last part of the formula in the example ROWS($B$3:B3). Notice that the first cell is locked the other is not, so when you copy the formula down it should change to ROWS($B$3:B4). Change range to match your data if needed.

If that's not the problem post a copy of the formula you are using.

 

[baker_89]

I got it to work, I was putting the array within a range of cells but when I put it in just one cell and dragged it down it worked perfectly. Thank you for your help! !

 

[AhoyNC]

You're welcome. Glad you got it to work.
You may find these links to be of interest.

https://blog.udemy.com/vlookup-multiple-values/

Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value