4

I have a table on a worksheet called "DealSetup" that looks like this simplified version:

   A           B
1  John Doe    1
2  Jane Doe    1
3  Mark Doe    2
4  Doug Doe    1
5  Mary Doe    2

I have a table on a worksheet called "List" that I would like to pull the names into from the "DealSetup" worksheet. However, I want only list the names based on the value in column B.

For example, on the "List" worksheet, I'd like to use some sort of INDEX or array function of some kind to look to the "DealSetup" sheet and pull everyone who's column B value is 1 and list them. I do not want any blanks, though. It should look like this:

 EVERYONE WITH B COLUMN VALUE OF 1:  
   A  
1  John Doe
2  Jane Doe
3  Doug Doe

Or like this:

 EVERYONE WITH B COLUMN VALUE OF 2:  
   A  
1  Mark Doe
2  Mary Doe

I've been trying a few things with no luck. Not sure where to go from here, so any input would be appreciated!!

Engineer Toast
  • 5,218
drumichael611
  • 43

2 Answers2

4

Assuming your data in DealSetup!A$2:B$6 you can use this formula in List!A2

=IFERROR(INDEX(DealSetup!A$2:A$6,SMALL(IF(DealSetup!B$2:B$6=C$1,ROW(DealSetup!A$2:A$6)-ROW(DealSetup!A$2)+1),ROWS(A$2:A2))),"")

confirm with CTRL+SHIFT+ENTER and copy down as far as required and further. When you run out of names you get blanks.

Assumes criteria (1,2 etc.) in C1

see attached workbook - try changing C1 to 2

If names repeat (within the specified criteria) then you'll get the names repeated - formula can be tweaked to show each name once only, i.e. this version

=IFERROR(INDEX(DealSetup!A$2:A$6,SMALL(IF(DealSetup!B$2:B$6=C$1,IF(COUNTIF(A$1:A1,DealSetup!A$2:A$6)=0,ROW(DealSetup!A$2:A$6)-ROW(DealSetup!A$2)+1)),1)),"")

barry houdini
  • 11,212
-1

If you want a solution with formulas, then you're going to have blanks at the bottom. There's no way to have the cells containing the formula grow and shrink as your list changes. Your table with formulas should be the same height as your raw data to ensure that you don't miss anything if, say, everybody has a 1 in column B. If you know that'll never be the case, you can reduce the number of rows in the List worksheet but it's a risk. Here's a formula that'll return them all in order with blanks at the bottom. It's an array formula so enter it with Ctrl+Shift+Enter: (Barry Houdini already submitted a similar solution)

{=IFERROR(INDEX(DealSetup!$A:$A,SMALL(IF(DealSetup!$B:$B=1,ROW(DealSetup!$B:$B)),ROW())),"")}

If you can't accept blanks at the bottom, it'll have to be either a PivotTable or VBA solution. This question is pretty old so I don't expect to see OP back here any time soon, unfortunately.

Engineer Toast
  • 5,218