Lookup a non natural number eg 4-1020 within a range and return value in next column

[Dares2]

Hello this is probably quite simple but I am looking at a vlookup that finds if a non natural number eg 4-4402 is within a range (column a and b) and returns the value in column c

4-4402
4-1000	4-3199	Service Agreements - Recurring
4-3200	4-3300	Service Agreements - Non Recurring
4-4680	4-4699	Fees from Training Activities
4-4541	4-4545	Corporate Partnerships
4-4400	4-4499	Donations Received

 

[Peter_SSs]

Does this do what you want?

24 11 12.xlsm
	A	B	C	D	E	F
1					Lookup Value	Result
2	4-1000	4-3199	Service Agreements - Recurring		4-4402	Donations Received
3	4-3200	4-3300	Service Agreements - Non Recurring		4-4501	N/A
4	4-4680	4-4699	Fees from Training Activities		5-3250	N/A
5	4-4541	4-4545	Corporate Partnerships		4-3250	Service Agreements - Non Recurring
6	4-4400	4-4499	Donations Received		4-4546	N/A
7					4-4545	Corporate Partnerships
Check Range

Cell Formulas
Range	Formula
F2:F7	F2	=FILTER(C$2:C$6,(E2>=A$2:A$6)*(E2<=B$2:B$6),"N/A")

 

[Dares2]

Does this do what you want?

24 11 12.xlsm
	A	B	C	D	E	F
1					Lookup Value	Result
2	4-1000	4-3199	Service Agreements - Recurring		4-4402	Donations Received
3	4-3200	4-3300	Service Agreements - Non Recurring		4-4501	N/A
4	4-4680	4-4699	Fees from Training Activities		5-3250	N/A
5	4-4541	4-4545	Corporate Partnerships		4-3250	Service Agreements - Non Recurring
6	4-4400	4-4499	Donations Received		4-4546	N/A
7					4-4545	Corporate Partnerships
Check Range

Cell Formulas
Range	Formula
F2:F7	F2	=FILTER(C$2:C$6,(E2>=A$2:A$6)*(E2<=B$2:B$6),"N/A")

Thankyou for this. I seem to get a spill error from this formula. Im not sure why. Either way I think I have solved it with an xlookup formula

 

[Peter_SSs]

I seem to get a spill error from this formula.

That should only happen if the value being looked up (the value in column E in my layout) fitted into more than one of the groups in columns A:B. Could that be a possibility?
If so, which result should be returned?

It would be good to see a small set of sample data with XL2BB that shows the spill error with my formula.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

In the interim it would be interesting to know if this modification returned the correct results for you.

24 11 12.xlsm
	A	B	C	D	E	F
1					Lookup Value	Result
2	4-1000	4-3199	Service Agreements - Recurring		4-4402	Donations Received
3	4-3200	4-3300	Service Agreements - Non Recurring		4-4501	N/A
4	4-4680	4-4699	Fees from Training Activities		5-3250	N/A
5	4-4541	4-4545	Corporate Partnerships		4-3250	Service Agreements - Non Recurring
6	4-4400	4-4499	Donations Received		4-4546	N/A
7					4-4545	Corporate Partnerships
Check Range

Cell Formulas
Range	Formula
F2:F7	F2	=TAKE(FILTER(C$2:C$6,(E2>=A$2:A$6)*(E2<=B$2:B$6),"N/A"),1)

 

[Peter_SSs]

I seem to get a spill error from this formula.

To check, I have set up data with the layout and codes as shown in your image in post 3 and adapted the ranges in my post 2 formula to suit that layout and I don't get any spill error.

24 11 12.xlsm
	A	B	C	D	E	F	G
1
2
3	4-3101		A		4-1000	4-3199	A
4	4-4425		G		4-3200	4-3300	B
5	4-4543		F		4-3400	4-3400	C
6	4-4510		H		4-3420	4-3420	D
7	4-4511		H		4-4680	4-4699	E
8	4-4541		F		4-4541	4-4545	F
9	4-4545		F		4-4400	4-4499	G
10	4-4544		F		4-4500	4-4540	H
11	4-4543		F		4-4600	4-4679	I
12					4-4715	4-4715	J
13					4-4720	4-4720	K
14					4-4710	4-4710	L
Check Range (2)

Cell Formulas
Range	Formula
C3:C11	C3	=FILTER(G$3:G$14,(A3>=E$3:E$14)*(A3<=F$3:F$14),"N/A")

 

[Dares2]

thanks for your help @Peter_SSs
. I tried the filter range and it did work. As I had worked it out though with a xlookup I used this as it was solved. Appreciate you looking at it.

 

[Peter_SSs]

I tried the filter range and it did work.

Thanks for confirming.

Appreciate you looking at it.

You are welcome.