PDA

View Full Version : Advanced Formula



wrightyrx7
12-16-2013, 06:32 AM
Hi all,

Sheet 1
Column A has a list of peoples names.
Column B has a list of ages.

Is it possible for me to have a formula on sheet2 that I can copy down to list all the people of a given age?

Obviously there would be part of the formula for me to edit with the age.

I know how to do this in VBA, but need to do it as a formula.

Any help would be great.

SamT
12-16-2013, 09:23 AM
IF(B1=>[Age],A1,"")

Bob Phillips
12-16-2013, 03:28 PM
This array formula will not leave any blanks in the results set

=IFERROR(INDEX(Sheet1!$A$1:$A$20,SMALL(IF(Sheet1!$B$1:$B$20=[Age],ROW(Sheet1!$A$1:$A$20)),ROW(A1))),"")

[Age] can refer to a cell with your target age, or hard-code it. Change the $20 to a number to encompass all of your first table, and copy the formula down as far as you think you might need, it will cater for fewer entries.