return multiple results from one column based on cells in another column

**chollyfunk** · 05-09-2012, 02:36 AM

I have a spreadsheet that lists names in the first column and then has columns to the right that say (for example) what work shift each person is on for that day. I'm looking for a formula that will look at one of the right hand columns for a specific shift (ie "LATE") and will then return all the names from the left column that correspond to that shift (hope this makes sense), this formula would then be applied to each column so that I end up with a list of names for each shift at the bottom of each days column.

How do I return these multiple results? All cells contain text only and are not alphabetical.

I've been looking for a solution for this for some time now, but none of the available options seems to work they either only work left to right (VLOOKUP), need to be alphabetical or require numerical data. I'm a total beginner when it comes to formulas so this is probably not helping...

Thanks

**Fotis1991** · 05-09-2012, 02:42 AM

Ho and welcome to the forum

It will better for all(first for you), if you upload a small sample workbook of your data.

**chollyfunk** · 05-09-2012, 03:17 AM

CHOLLYFUNK EXAMPLE WORKBOOK.xlsxHi Fotis, thank you for the welcome - I'm looking forward to this community now my job needs this sort of stuff - it's all new to me...

Here's an example workbook (hope I did that right) - should be much clearer than my muddled description

**canapone** · 05-09-2012, 05:02 AM

Hi (I'm a Excel 2000 user) in the example attache I'm using array formulae

=if(iserror(Index($A$3:$A$51,small(if(B$3:B$51=$A$56,Row($A$3:$A$51)-2),Row(A1)))),"",Index($A$3:$A$51,small(if(B$3:B$51=$A$56,row($A$3:$A$51)-2),Row(A1))))

In Excel 2007 and later (your option) the formula can be dried as follows:

=Iferror(Index($A$3:$A$51,small(if(B$3:B$51=$A$56,Row($A$3:$A$51)-2),Row(A1)))),"")

In the formulae row(a1) is the counter that helps the formula to get the first - Row(a1)- the second - Row(a2)- name you need to extract.

All the formulae are confirmed with control+shift+enter.

The formulae consider column A (names)=column I=column Q..

Regards

**chollyfunk** · 05-09-2012, 05:08 AM

Holy Cow Canapone! That looks like it's working perfectly - thank you so much.

Now I need to sit down and try to understand exactly what it's doing, but for now you've just made my job so much easier.

Thanks again.

**canapone** · 05-09-2012, 05:20 AM

Hi,

later in the afternoon (in 6/7 hours) I'll have at disposal Excel 2011 too.

I'll upload in my previous message the IFERROR version of the formulae (same results, more efficiency, less pollution).

If you have problems in adjusting the formulae do not hesitate to share the matter to the Forum.

Thanks for the kind feedback.

Regards