Excel 2007 : Return ALL values for multiple identifying factors.

**Sarahsodapop** · 07-19-2011, 10:02 AM

Hello all,

I have a spreadsheet with two tabs --- one of which has a list of names (column B) with an identifying # next to it (column A, numbers 1-14 - or no number) and dates that correspond to the name (column C). The other tab has a subset of those identifying numbers (ex. 3,4,5,6) in one column.

Through an INDEX/array formula I have managed to return all names with the identifying value #3, but cannot manage to get it to return all names with the values 3,4,5, or 6. Current formula (entered as an array) is here: =INDEX('2011 Schedule'!A:B,SMALL(IF('2011 Schedule'!A:A=List!$E$27,ROW('2011 Schedule'!$A:$A)),ROW(1:1)),2)

Ideally, I would like it not only to return all names that have the identifying numbers within the set above it (i.e. 3,4,5,6), but also to return the names in date order (from column C on the first tab).

I have attached a spreadsheet that hopefully will illustrate more fully. I am using Excel 2007.

Any help would be greatly appreciated!!!

Thanks,
Sarah

**NBVC** · 07-20-2011, 08:35 AM

Try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER.

Note: You should not use whole column references with this array formula. Try to keep the row range size to a minimum necessary.