Return a List

**jasonb9** · 07-02-2012, 08:44 AM

I have a spreadsheet with two worksheets. One Worksheet has a list of 100 or so last names (column 1), first names (column 2) and ages (column 3).

In my second worksheet, I'd like to have a formula that returns a list of last names and first names of those over the age of 21.

I used to know how but I can't seem to figure it out now. I've tried, vlookup but can't make it work. Can someone please help me?

Thanks,
Jason B
Fairfax, VA

**tom1977** · 07-02-2012, 09:03 AM

Hi try formula from attachment (this is array formula so You must cofirm this with CTRL + SHIFT + ENTER)

**jasonb9** · 07-02-2012, 09:09 AM

Tom,

I can see that works but is that simplest way to do it? Is there another formula that will return a list from another sheet when a value is greater than?

**tom1977** · 07-02-2012, 09:11 AM

Sorry I do not know simpler formula and what about pivot table?

**jasonb9** · 07-02-2012, 09:13 AM

I can research the pivot table. Thanks for your help?

**tom1977** · 07-02-2012, 09:17 AM

Sorry pivot table is not good idea

try to find something about advance filter

**Pepe Le Mokko** · 07-02-2012, 09:20 AM

Ypu can copy paste your range to another sheet then apply then sort your range for ages smaller than 21 than delete the results and remove the filter

( although Tom's solution is more interesting)

**jason.b75** · 07-02-2012, 09:30 AM

You can shrink Tom's formula slightly,

=IFERROR(INDEX(A$1:A$5,SMALL(IF($C$1:$C$5>$D$2,ROW($C$1:$C$5),""),ROW()),"")

But that's as simple as it can get.