Lookup help please.

[courtmaster1998]

I have been looking for a excel formula that would give me the following:

Col A. COL B. COL C. Col D. Col E
1.. $1. $5. Red. $8. Orange
2. $6. $10. Orange
3. $11. $15. Pink
4. $16. $20. Purple

And so on

Column A & B are the ranges. Column C is the output color for any number in that range

D1 is a supplied number to find within the ranges and then when located what the color should be as the output answer.

 

[Peter_SSs]

Welcome to the MrExcel board!

Here are some options that you could consider.

23 05 04.xlsm
	A	B	C	D	E	F	G
1	1	5	Red	8	Orange	Orange	Orange
2	6	10	Orange	5	Red	Red	Red
3	11	15	Pink	17	Purple	Purple	Purple
4	16	20	Purple
Lookup

Cell Formulas
Range	Formula
E1:E3	E1	=XLOOKUP(D1,A$1:A$4,C$1:C$4,,-1)
F1:F3	F1	=VLOOKUP(D1,A$1:C$4,3)
G1:G3	G1	=INDEX(C$1:C$4,MATCH(D1,A$1:A$4))

 

[courtmaster1998]

Welcome to the MrExcel board!

Here are some options that you could consider.

23 05 04.xlsm
	A	B	C	D	E	F	G
1	1	5	Red	8	Orange	Orange	Orange
2	6	10	Orange	5	Red	Red	Red
3	11	15	Pink	17	Purple	Purple	Purple
4	16	20	Purple
Lookup

Cell Formulas
Range	Formula
E1:E3	E1	=XLOOKUP(D1,A$1:A$4,C$1:C$4,,-1)
F1:F3	F1	=VLOOKUP(D1,A$1:C$4,3)
G1:G3	G1	=INDEX(C$1:C$4,MATCH(D1,A$1:A$4))

Hi Peter,
Thank you for the quick response. It works excellent. Only problem I have, if D1 falls before the first range A1:B1, I get #N/A. How can a fix it so that the output is $0?

Regards, Tim

 

[Peter_SSs]

if D1 falls before the first range A1:B1, I get #N/A. How can a fix it so that the output is $0?

It depends which of the 3 suggestion you chose. Again options

23 05 04.xlsm
	A	B	C	D	E	F	G
1	Low	High	$	Input
2	10	14	100	8	0	0	0
3	15	19	200
4	20	24	300
5	25	29	400
Lookup (2)

Cell Formulas
Range	Formula
E2	E2	=XLOOKUP(D2,A$2:A$5,C$2:C$5,0,-1)
F2	F2	=IFNA(VLOOKUP(D2,A$2:C$5,3),0)
G2	G2	=IFNA(INDEX(C$2:C$5,MATCH(D2,A$2:A$5)),0)

Another choice would be to add a new first row to the lookup table and then you could use the original formula(s)

23 05 04.xlsm
	A	B	C
1	Low	High	$
2	0	9	0
3	10	14	100
4	15	19	200
5	20	24	300
6	25	29	400
Lookup (3)