Reference Formula Returning Multiple Matches

[Meth0dman27]

Hi All,

Looking for a possible answer to an excel issue I'm having. I would need to be able to return multiple values using an index search, but also using multiple criteria. I have two sheets that I'm working with

First sheet is set up as such:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Shift[/TD]
[TD]Section[/TD]
[TD]Employee #[/TD]
[TD]Name[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[/TR]
[TR]
[TD]CKFT[/TD]
[TD]CK[/TD]
[TD]111[/TD]
[TD]John[/TD]
[TD]6am[/TD]
[TD]off[/TD]
[/TR]
[TR]
[TD]CKFT[/TD]
[TD]CK[/TD]
[TD]222[/TD]
[TD]Jane[/TD]
[TD]off[/TD]
[TD]6am[/TD]
[/TR]
[TR]
[TD]HKFT[/TD]
[TD]HK[/TD]
[TD]333[/TD]
[TD]Joe[/TD]
[TD]6am[/TD]
[TD]VAC[/TD]
[/TR]
[TR]
[TD]HKFT[/TD]
[TD]HK[/TD]
[TD]444[/TD]
[TD]Joanne[/TD]
[TD]6am[/TD]
[TD]6am[/TD]
[/TR]
[TR]
[TD]HKFT[/TD]
[TD]HK[/TD]
[TD]555[/TD]
[TD]Jim[/TD]
[TD]VAC[/TD]
[TD]3pm[/TD]
[/TR]
</tbody>[/TABLE]


This is a pretty simplified version as it spans multiple sections, days and employees

The issue I'm having is trying to populate another sheet using this data source. What I want to do is be able to use a formula to populate the names into the below format. The qualifying criteria that would be used are listed at the top and can be assumed to just be A2,B2,C2 respectively

Sheet 2:

DATE: Monday
SECTION: HK
SHIFT: 6AM (search for "6AM" text string within Column E & F)

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]EMP no.[/TD]
[TD]NAME[/TD]
[TD]HR[/TD]
[TD]OT[/TD]
[TD]Comment[/TD]
[TD]Replace[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Joe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Joanne[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I'm looking to solve for only column B in this chart. The only formula I've tried so far was an array formula, however it didn't allow to solve for two lookup criteria, and returned an error only.

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Shift[/td][td]Section[/td][td]Employee #[/td][td]Name[/td][td]Monday[/td][td]Tuesday[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]CKFT[/td][td]CK[/td][td]

111​

[/td][td]John[/td][td]6am[/td][td]off[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]CKFT[/td][td]CK[/td][td]

222​

[/td][td]Jane[/td][td]off[/td][td]6am[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]HKFT[/td][td]HK[/td][td]

333​

[/td][td]Joe[/td][td]6am[/td][td]VAC[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]HKFT[/td][td]HK[/td][td]

444​

[/td][td]Joanne[/td][td]6am[/td][td]6am[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]HKFT[/td][td]HK[/td][td]

555​

[/td][td]Jim[/td][td]VAC[/td][td]3pm[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]DATE: [/td][td]Monday[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]SECTION: [/td][td]HK[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]SHIFT: [/td][td]6AM[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]A[/td][td]B[/td][td]C[/td][td]D[/td][td]E[/td][td]F[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]EMP no.[/td][td]NAME[/td][td]HR[/td][td]OT[/td][td]Comment[/td][td]Replace[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td][/td][td]Joe[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td][/td][td]Joanne[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet69[/td][/tr][/table]

Array formula in cell B14:
=INDEX($D$2:$D$6,SMALL(IF(($E$1:$F$1=$B$8)*($E$2:$F$6=$B$10)*($B$2:$B$6=$B$9), MATCH(ROW($D$2:$D$6), ROW($D$2:$D$6)),""), ROWS($A$1:A1)))