Excel formula - querying a Range that results a Range

I have a Range in Excel (B3:C8) from which I want to filter out the English persons. In SQL this would be dead simple:
SELECT Persons FROM [myTable] WHERE Nationality = 'English'

How can I apply a similar filtering on a Range where the result is not a single value but a Range?
Remark: Excel has a Filter button, but all it does is HIDES the unwanted rows. I do not want hidden rows.

This is how I want my table to look like. What should the formula of G3 look like?

enter image description here

user24752

Posted 2011-12-29T20:15:31.483

Reputation: 253

Answers

To get the names into a range, you could make your data a table and then create a pivot table with Nationality as the report filter and Persons as the row label. Then choose English from the nationality list. See screen shot below (ignore column D as it was not used);

Pivot Table

CharlieRB

Posted 2011-12-29T20:15:31.483

Reputation: 21 303

You don't need to create a pivot table to filter on the Nationality. A simple filter of the Nationality column would have suffice and he mentioned he didn't actually want to hide rows(filtering), he wants to delete them completely. – Jay – 2011-12-29T22:05:15.783

1@Jay I don't see the words "delete" in the question. Also, a simple filter does hide rows. Lastely, there may be many ways of accomplishing the same thing when asked; 'This is how I want my table to look like. What should the formula of G3 look like?' – CharlieRB – 2011-12-29T23:20:56.180

I think a pivot table is a good solution here. +1 – Doug Glancy – 2011-12-29T23:48:31.090

In the question, he said, "Remark: Excel has a Filter button, but all it does is HIDES the unwanted rows. I do not want hidden rows." Reading between the lines, what he wants is to have a column with the names that fit the criteria and to have the other names not exist in that column. And don't get me wrong, pivot tables are great but I think for such a simple table, it's not worth making a pivot table. On another note, Doug Glancy's answers exactly what is being asked. – Jay – 2011-12-30T14:13:23.993

Enter this in G3 and drag down. It's an array formula, so must be entered using Ctrl Shft Enter

=IFERROR(INDEX($B$3:$B$8,LARGE(($C$3:$C$8=$E$3)*(ROW($B$3:$B$8)-2),COUNTIF($C$3:$C$8,$E$3)-(ROWS($3:3)-1))),"")

Note, IfError is only available in XL 2007/10, otherwise, you'll need to use:

=IF(ISERROR(INDEX($B$3:$B$8,LARGE(($C$3:$C$8=$E$3)*(ROW($B$3:$B$8)-2),COUNTIF($C$3:$C$8,$E$3)-(ROWS($3:3)-1)))),"",INDEX($B$3:$B$8,LARGE(($C$3:$C$8=$E$3)*(ROW($B$3:$B$8)-2),COUNTIF($C$3:$C$8,$E$3)-(ROWS($3:3)-1))))

Doug Glancy

Posted 2011-12-29T20:15:31.483

Reputation: 1 756

Doug, I wanted exactly something like that. Thank you for the great effort, it works perfectly. However, I still mark the pivot table as solution. The reason is maintainability. This formula is very complex already for our textbook example. When applying in real life scenario, its complexity explodes further. Pivot table is not as perfect as this, but very simple to use. Thank you again! – user24752 – 2012-01-01T17:08:21.277

I agree wholeheartedly. I like to figure out formulas like this, but I seldom end up using the complex ones. In the vast jumbled landscape that is Excel, pivot tables are a rock of stability. – Doug Glancy – 2012-01-01T20:30:45.197

This version will work in any version of Excel and gives the results in the order listed

In G3:

=IF(ROWS(G$3:G3)>COUNTIF(C$3:C$8,E$3),"",INDEX(B$3:B$8,SMALL(IF(C$3:C$8=E$3,ROW(C$3:C$8)-ROW(C$3)+1),ROWS(G$3:G3))))

confirmed with CTRL+SHIFT+ENTER (pressed together) and copied down as far as required

barry houdini

Posted 2011-12-29T20:15:31.483

Reputation: 10 434

There is an error in the formula. If I turn E3 to 'German', John stays in G3 instead of Hans.The same with selecting 'French', Luis does not show up, but John takes his place. – user24752 – 2012-01-06T19:46:17.307

Works fine for me.....did you confirm with CTRL+SHIFT+ENTER and then copy down? If I change E3 to German I see Hans and Jurgen as expected..... – barry houdini – 2012-01-06T20:31:05.670

Sorry, my fault! You are right. – user24752 – 2012-01-07T20:36:39.893

One way would be to use this IF statement =IF($E$3=C3,B3,"") which would give you the persons name if the Nationality matches the filter or a blank if it does not.

N4TKD

Posted 2011-12-29T20:15:31.483

Reputation: 979

=If($E$3=English,B3,"")

You can make this more generalized by doing:

=If($E$3=C3,B3,"")

soandos

Posted 2011-12-29T20:15:31.483

Reputation: 22 744