Excel 2010 Assistance needed with Index Match Returning Multiple Results

[Donager]

Good day and thank you for taking the time to review my request.

I am using Excel 2010 on Windows 7 and I have searched the forums extensively and attempted to make the formula work.

I have a data dump of time off requests with the following format:



On a separate tab on the spreadsheet, I am using Index/Match to search by date to return the first match. I want all names to populate that have the same scheduled date.

I created the following formula after reading and researching the formula and quite honestly, I think I got out of my league with my Excel understanding. I am moderately good and can use basic lookups and pivot tables, but I don't have the formalized understanding that others may.

This is the formula I created

=IF(COUNTIF(Sheet1!E:E,'Apr 15'!$A$1)>=ROWS($A$2:A2),INDEX(Sheet1!B:B,SMALL(IF(Sheet1!E:E='Apr 15'!A1,ROW(Sheet1!B:B)-ROW(Sheet1!E2)+1,ROWS($A$2:A2)),"")))

I turned it into an array using CTRL, SHFT, ENTR and it has the curly braces around it.

This is how it looks in Excel



I believe my issue is syntax and a lack of understanding of the various elements of the formula.

If someone could please review my request and provide me some guidance, I would greatly appreciate it.

Respectfully,

Donager

 

[Matty]

Hi and welcome to MrExcel!

Better to post tables that can be copied and worked with rather than just pictures. See the attached for some recommended add-ins for doing this:

http://www.mrexcel.com/forum/excel-questions/628649-recommended-add-ins-links.html

You're more likely to get help if potential helpers are able to easily extract the data.

Matty

 

[Donager]

Thank you, Matty. Ill update accordingly.

 

[Donager]

I have replaced the images in my post with the content from the MrExcel HTML Generator.

Thank you for your time.

Good day and thank you for taking the time to review my request.

I am using Excel 2010 on Windows 7 and I have searched the forums extensively and attempted to make the formula work.

I have a data dump of time off requests with the following format:

Excel 2010

	A	B	C	D	E	F	G
1	Status	From	Request Type	Type	Scheduled Date	Received Date	DOW
2	Denied	Bob	Exception	Vacation	1/1/2015	2/25/2014 16:15	Thursday
3	Denied	mike	Exception	Vacation	1/1/2015	1/14/2014 11:13	Thursday
4	Denied	george	Exception	Vacation	1/1/2015	2/18/2014 20:02	Thursday
5	Denied	sue	Exception	Vacation	1/2/2015	2/25/2014 16:25	Friday
6	Approved	bill	Exception	Vacation	1/2/2015	10/24/2013 14:24	Friday
7	Denied	william	Exception	Vacation	1/2/2015	10/20/2014 15:58	Friday
8	Approved	henry	Exception	Vacation	1/2/2015	11/12/2014 16:27	Friday
9	Denied	Bob	Exception	Vacation	1/2/2015	12/6/2013 14:46	Friday
10	Denied	mike	Exception	Vacation	1/2/2015	1/14/2014 11:13	Friday
11	Denied	george	Exception	Vacation	1/2/2015	2/18/2014 20:02	Friday
12	Denied	sue	Exception	Personal	1/2/2015	11/18/2014 7:45	Friday
13	Approved	bill	Exception	Vacation	1/2/2015	10/24/2013 16:22	Friday
14	Denied	william	Exception	Vacation	1/3/2015	10/20/2014 15:58	Saturday
15	Approved	henry	Exception	Vacation	1/3/2015	10/24/2013 14:24	Saturday
16	Approved	Bob	Exception	Vacation	1/3/2015	11/12/2014 16:27	Saturday
17	Approved	mike	Exception	Vacation	1/3/2015	1/14/2014 11:14	Saturday
18	Denied	george	Exception	Vacation	1/3/2015	3/29/2014 11:36	Saturday
19	Approved	sue	Exception	Vacation	1/3/2015	2/25/2014 16:26	Saturday

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

On a separate tab on the spreadsheet, I am using Index/Match to search by date to return the first match. I want all names to populate that have the same scheduled date.

I created the following formula after reading and researching the formula and quite honestly, I think I got out of my league with my Excel understanding. I am moderately good and can use basic lookups and pivot tables, but I don't have the formalized understanding that others may.

This is the formula I created

=IF(COUNTIF(Sheet1!E:E,'Apr 15'!$A$1)>=ROWS($A$2:A2),INDEX(Sheet1!B:B,SMALL(IF(Sheet1!E:E='Apr 15'!A1,ROW(Sheet1!B:B)-ROW(Sheet1!E2)+1,ROWS($A$2:A2)),"")))

I turned it into an array using CTRL, SHFT, ENTR and it has the curly braces around it.

This is how it looks in Excel

Excel 2010

	A	B	C	D	E	F
1	1/1/2015	1/2/2015	1/3/2015	1/4/2015	1/5/2015	1/6/2015
2	FALSE
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Jan 15​

I believe my issue is syntax and a lack of understanding of the various elements of the formula.

If someone could please review my request and provide me some guidance, I would greatly appreciate it.

Respectfully,

Donager

 

[Weazel]

=IF(COUNTIF(Sheet1!E:E,'Apr 15'!$A$1)>=ROWS($A$2:A2),INDEX(Sheet1!B:B,SMALL(IF(Sheet1!E:E='Apr 15'!A1,ROW(Sheet1!B:B)-ROW(Sheet1!E2)+1,ROWS($A$2:A2)),"")))

try it like this.....

=IF(ROWS(A$2:A2)>COUNTIF(Sheet1!$E$2:$E$19,A$1),"",INDEX(Sheet1!$B$2:$B$19,SMALL(IF(Sheet1!$E$2:$E$19=A$1,ROW(Sheet1!A$2:A$19)-ROW(Sheet1!A$2)+1),ROWS(A$2:A2)))) control shift enter

you should also try to avoid the full column references also

 

[Matty]

In B2 of Jan 15, try:

=IF(ROWS(A$2:A2)>COUNTIF(Sheet1!$E$2:$E$19,A$1),"",INDEX(Sheet1!$B$2:$B$19,SMALL(IF(Sheet1!$E$2:$E$19=A$1,ROW(Sheet1!$B$2:$B$19)-ROW(Sheet1!$B$2)+1),ROWS(A$2:A2))))

Committed with CTRL+SHIFT+ENTER, before copying down and across.

Matty

 

[Donager]

It works!

Weazel and Matty, thank you so much. I spent a long time on this and you took a few minutes.

Weazel, I will remove the full column references. I was being lazy.

I really appreciate this. You just made life a lot easier for me

Have a great day.

 

[Matty]

Happy to help. Come back any time.

Matty