Lookup value within range

[arnabmit]

I have data table which has [Sub-Cat ID].

Another mapping table has [Category ID] [Category Name] [Sub-Cat Start] [Sub-Cat End]

I want to insert a new column in the data table which will lookup the corresponding [Category ID] for each [Sub-Cat ID] where [Sub-Cat ID] >= [Sub-Cat Start] AND [Sub-Cat ID] < [Sub-Cat End]

Please help, I'm lost!

 

[Peter_SSs]

I'm lost!

So am I. Could we have some sample data and expected results with XL2BB and explain again in relation to the sample data?

 

[arnabmit]

So am I. Could we have some sample data and expected results with XL2BB and explain again in relation to the sample data?

Thanks for replying. XL2BB is blocked by Excel. Here are some screenshots instead.

If the value of column A falls between columns G and H, then corresponding value from column E should be returned

Expected output is as below:

 

[Fluff]

XL2BB is blocked by Excel.

Have a look here XL2BB Icons greyed out

 

[Peter_SSs]

If the col A value will always be in one of the sub-categories in the second table and the second table sub-categories are in ascending order like your sample then try ..

arnabmit.xlsm
	A	B	C	D	E	F	G	H
1
2	Sub-Cat ID	Name	Category ID		Category ID	Category Name	Sub-Cat Start	Sub-Cat End
3	1001	a	1000		1000	AB	1001	1010
4	1003	b	1000		1100	BC	1101	1110
5	1005	c	1000		1230	CD	1231	1239
6	1009	d	1000
7	1101	e	1100
8	1102	f	1100
9	1107	g	1100
10	1234	h	1230
11	1237	i	1230
Sheet1

Cell Formulas
Range	Formula
C3:C11	C3	=LOOKUP([@[Sub-Cat ID]],Table2[Sub-Cat Start],Table2[Category ID])

If the col A value may not be in any sub-category and/or the second table sub-categories may not all be in ascending order then a couple of options to try depending on which of your listed versions this needs to work in.

arnabmit.xlsm
	A	B	C	D	E	F	G	H	I
1
2	Sub-Cat ID	Name	Category ID	Category ID2		Category ID	Category Name	Sub-Cat Start	Sub-Cat End
3	1001	a	1000	1000		1000	AB	1001	1010
4	1003	b	1000	1000		1230	CD	1231	1239
5	1005	c	1000	1000		1100	BC	1101	1110
6	1009	d	1000	1000
7	1101	e	1100	1100
8	1102	f	1100	1100
9	1107	g	1100	1100
10	1234	h	1230	1230
11	1237	i	1230	1230
12	1120	j	Not found	Not found
13
Sheet1

Cell Formulas
Range	Formula
C3:C12	C3	=FILTER(Table2[Category ID],(Table2[Sub-Cat Start]<=[@[Sub-Cat ID]])*(Table2[Sub-Cat End]>=[@[Sub-Cat ID]]),"Not found")
D3:D12	D3	=IFERROR(INDEX(Table2[Category ID],AGGREGATE(15,6,(ROW(Table2[Category ID])-ROW(Table2[#Headers]))/((Table2[Sub-Cat Start]<=[@[Sub-Cat ID]])*(Table2[Sub-Cat End]>=[@[Sub-Cat ID]])),1)),"Not found")

 

[arnabmit]

If the col A value will always be in one of the sub-categories in the second table and the second table sub-categories are in ascending order like your sample then try ..

arnabmit.xlsm
	A	B	C	D	E	F	G	H
1
2	Sub-Cat ID	Name	Category ID		Category ID	Category Name	Sub-Cat Start	Sub-Cat End
3	1001	a	1000		1000	AB	1001	1010
4	1003	b	1000		1100	BC	1101	1110
5	1005	c	1000		1230	CD	1231	1239
6	1009	d	1000
7	1101	e	1100
8	1102	f	1100
9	1107	g	1100
10	1234	h	1230
11	1237	i	1230
Sheet1

Cell Formulas
Range	Formula
C3:C11	C3	=LOOKUP([@[Sub-Cat ID]],Table2[Sub-Cat Start],Table2[Category ID])

If the col A value may not be in any sub-category and/or the second table sub-categories may not all be in ascending order then a couple of options to try depending on which of your listed versions this needs to work in.

arnabmit.xlsm
	A	B	C	D	E	F	G	H	I
1
2	Sub-Cat ID	Name	Category ID	Category ID2		Category ID	Category Name	Sub-Cat Start	Sub-Cat End
3	1001	a	1000	1000		1000	AB	1001	1010
4	1003	b	1000	1000		1230	CD	1231	1239
5	1005	c	1000	1000		1100	BC	1101	1110
6	1009	d	1000	1000
7	1101	e	1100	1100
8	1102	f	1100	1100
9	1107	g	1100	1100
10	1234	h	1230	1230
11	1237	i	1230	1230
12	1120	j	Not found	Not found
13
Sheet1

Cell Formulas
Range	Formula
C3:C12	C3	=FILTER(Table2[Category ID],(Table2[Sub-Cat Start]<=[@[Sub-Cat ID]])*(Table2[Sub-Cat End]>=[@[Sub-Cat ID]]),"Not found")
D3:D12	D3	=IFERROR(INDEX(Table2[Category ID],AGGREGATE(15,6,(ROW(Table2[Category ID])-ROW(Table2[#Headers]))/((Table2[Sub-Cat Start]<=[@[Sub-Cat ID]])*(Table2[Sub-Cat End]>=[@[Sub-Cat ID]])),1)),"Not found")

Thank you so much! This is truly helpful.

 

[Peter_SSs]

You're welcome. Glad it helped. Thanks for the follow-up.

Don't forget to check out @Fluff's link re XL2BB if you have further questions. You will generally get much faster/better suggestions as most helpers are not that keen on manually typing out data to test with.