Help Needed in Returning Values when looking up between 2 Numbers

Not sure how to explain the help I need. So I have a list of names with their ages... such as

Mark 24
Steve 35
Bob 42
Dave 45
Jack 52
Dean 55
Dan 60

My query is if I have 2 values say 30 and 45

Then I would like the function to return the names of Steve, Bob, Dave, since they are between the ages of 30 and 45

I need a formula that would return all of these names either in 1 cell or in seperate cells...

Thank you

Name is B, Age is C but this can be moved around if you need the age B and Name C

Please attach a sample workbook.

	A	B	C	D	E	F	G	H
1	name	age			name		age min	age max
2	Mark	24			Steve		30	45
3	Steve	35			Bob
4	Bob	42			Dave
5	Dave	45
6	Jack	52
7	Dean	55
8	Dan	60

E2=IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,ROW($A$2:$A$100)-ROW($A$2)+1/($B$2:$B$100>=$G$2)/(($B$2:$B$100<=$H$2)),ROWS($A$2:A2))),"")

Copy down

Here you go

CARACALLA has provided a solution in post #5.

please see my sample workbook, i appreciate his solution but I was hoping for something different.

Replacement


F4=IFERROR(INDEX(Spares!$B$2:$B$100,AGGREGATE(15,6,ROW(Spares!$B$2:$B$100)-ROW(Spares!$B$2)+1/(Spares!$C$2:$C$100>=Replacement!$B$4)/(Spares!$C$2:$C$100<=Replacement!$C$4),COLUMNS(Replacement!$A$1:Replacement!A1))),"")

Copy across

The formula above does not work, I am wondering if it's because I am working in google sheets and the formula does not work the same way

It's an array formula, try entering with Ctrl-Shift-Enter.

Originally Posted by joerainone

please see my sample workbook, i appreciate his solution but I was hoping for something different.

With Excel 2010, this could be the only solution. If you have 365, you could use the formula:=FILTER(B2:B8,IF(C2:C8>=25,C2:C8<=45))

Replacement

E4=TEXTJOIN(",",TRUE,IF((Spares!C2:C100>=Replacement!B4)*(Spares!C2:C100<=Replacement!C4),Spares!B2:B100,""))

Control+shift+enter