Index Match with Data Rage

[jsical]

I am trying to find a Index Match formula or any other formula that allows me look the value given the Year and the Size from the table on the right. i.e Year=2023 & Size 150, Answer= 15. i have try multiple index match option but i am not sure how to a data range, i.e if Size is between 50-100. Any help its greatly appreciated.

 

[Saba Sabaratnam]

You could try this formula in F9.

=XLOOKUP(A2,F2:L2,XLOOKUP(B2,E3:E7,F3:L7,,1))

Kind regards

Saba

 

[jsical]

You could try this formula in F9.

=XLOOKUP(A2,F2:L2,XLOOKUP(B2,E3:E7,F3:L7,,1))

View attachment 48848

Kind regards

Saba

Thank you, that is the formula i am using but, i am trying to figure if there is a way to have a data range, i.e if B2>=50 & B2<=100. without having to recreated the table.

 

[Saba Sabaratnam]

You could use array as shown below.

XLOOKUP(A2,F2:L2,XLOOKUP(B2,{50;100;500;1000;5000},F3:L7,,1))

Kind regards

Saba

 

[jsical]

Thank you, that is the formula i am using but, i am trying to figure if there is a way to have a data range, i.e if B2>=50 & B2<=100. without having to recreated the table.

You could use array as shown below.

XLOOKUP(A2,F2:L2,XLOOKUP(B2,{50;100;500;1000;5000},F3:L7,,1))

Kind regards

Saba

Thank you but getting an Value Error.

 

[jsical]

unfortunately, I get a value error. I have added a new column for the size ranges, but i cant figure out how to solve the issue. I want the formula to look up the value corresponding to the year and the size, but the size its a range. In this sample it should return 20, given that the size falls between 51-100 and the year is 2024.

 

[AhoyNC]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Year	Size	Answer		Size		2022	2023	2024	2025	2026	2027	2028	2029	2030	2031
2	2024	55	19		20	50	29	27	25	5	7	30	30	24	18	8
3					51	100	21	20	19	23	3	5	27	3	19	11
4					101	500	14	15	15	28	12	24	17	12	26	16
5					501	1000	6	9	10	17	9	15	3	19	25	24
6					1001	10000	3	6	7	8	26	14	5	27	27	24
Sheet1

Cell Formulas
Range	Formula
C2	C2	=INDEX($G$2:$P$6,XMATCH($B$2,$F$2:$F$6,1),MATCH($A$2,$G$1:$P$1,0))

 

[jsical]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Year	Size	Answer		Size		2022	2023	2024	2025	2026	2027	2028	2029	2030	2031
2	2024	55	19		20	50	29	27	25	5	7	30	30	24	18	8
3					51	100	21	20	19	23	3	5	27	3	19	11
4					101	500	14	15	15	28	12	24	17	12	26	16
5					501	1000	6	9	10	17	9	15	3	19	25	24
6					1001	10000	3	6	7	8	26	14	5	27	27	24
Sheet1

Cell Formulas
Range	Formula
C2	C2	=INDEX($G$2:$P$6,XMATCH($B$2,$F$2:$F$6,1),MATCH($A$2,$G$1:$P$1,0))

thank you very much!! that work perfect

 

[AhoyNC]

You're welcome. Thanks for the feedback.