Using INDEX SMALL IF ROW to find multiple values

[rival2031]

Happy Friday all...

I am currently using this formula to look up several names given a single criteria:

{=IFERROR(INDEX(P:AX,SMALL(IF(AX:AX=$I$30,ROW(P:P)),ROW(1:1)),1),"")}

Formula works fine if based on 1 criteria only (here, $I$30). Question is whether the formula can be modified to use multiple criteria (actually a whole column of criteria which is I4:I99) which would actually look like:

{=IFERROR(INDEX(P:AX,SMALL(IF(AX:AX=$I$4:$I$99,ROW(P:P)),ROW(1:1)),1),"")}.

However, this modification is not returning any values when copied down. Anyone have any suggestions?

Thank you in advance...

[XOR LX]

Posting a workbook with a small sample would be a massive help.

Regards

[rival2031]

Makeshift example attached. Difference(s) here is

{=IFERROR(INDEX($A$1:$B$28,SMALL(IF($B$1:$B$28=$G$1,ROW($A$1:$A$28)),ROW(1:1)),1),"")} is the formula used to return Names that show up under Claim type "Disability" (see column E).

Now what's needed in column J is a list of names that correspond to column I. I know that

{=IFERROR(INDEX($A$1:$B$28,SMALL(IF($B$1:$B$28=$G$1,ROW($A$1:$A$28)),ROW(1:1)),1} where G1= any deparment I enter, but I want to enter the entire column as a criteria... but

{=IFERROR(INDEX($A$1:$B$28,SMALL(IF($B$1:$B$28=$I$2$:$I$97,ROW($A$1:$A$28)),ROW(1:1)),1}

will not work.

Any suggestions? Thanks!

[XOR LX]

Firstly, I'm a little confused. You say you want this condition: "IF($B$1:$B$28=$I$2$:$I$97", yet none of the entries in B1:B28 are to be found in I2:I97, and vice versa, so this condition will always be FALSE. I presume you mean column C?

Secondly, I'm not sure I understand what it is you wish to return. Can you give an example or two of what you would wish to see in column J, and why?

Regards

[rival2031]

Sorry about that... multi tasking on Friday is not a good idea. You are correct in presuming column C, I cut and pasted hastily and missed that correction. In column J, I need a Name returned for each time a department in column I matches with one from C. For example, Name1, Name2, Name3 all belong to "Nuclear Medicine" and need to show up in column J. 1 name for CT Scanner, 1 name for Lab - Support Services, 11 names for Surgical Pathology and so forth. For departments not matching from column I to column C, there would be no return as the condition was not met. But again IF($C$1:$C$28=$I$2:$I$97" does not work. Thanks for taking the time with me XOR LX!

[XOR LX]

Hi,

Apologies for late reply. Try this array formula in cell J2 and copied down:

=INDEX($A$1:$A$28,SMALL(IF(ISNUMBER(MATCH($C$2:$C$28,$I$2:$I$97,0)),ROW($C$2:$C$28)),ROWS($A$2:$A2)),1)

Hope that helps.

Regards