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.
IF(B1=>[Age],A1,"")
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
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.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Posting Permissions
Reply With Quote
