Find the match if given number is between the two ranges of number

[mark692]

hi guys I have two ranges of number in column D and column E, I want to get the fruit in column F if the given number on A2 is between the range of the numbers on column D and column E. I have a sample below. thank you

Book1
	A	B	C	D	E	F
1	Number	Fruit		Start	End	Fruits
2	8	grapes		1	5	apple
3				6	10	grapes
4				11	15	avocado
5				16	20	orange
6
7
Sheet1

 

[Trebor76]

Hi mark692,

Try this formula in cell B2:

=IF(ISNUMBER(A2)=TRUE,LOOKUP(A2,$D$2:$D$5,$F$2:$F$5),"")

Regards,

Robert

 

[kweaver]

How about:

=INDEX(F1:F5,SUMPRODUCT((A2>=D2:D5)*(A2<=E2:E5)*ROW(2:5)))

or

=LOOKUP(A2,D2:D5,F2:F5)

 

[mark692]

thank you so much guys all of this works perfectly

 

[Trebor76]

Thanks for letting us know and you're welcome