Index & Match matrix function. With number Intervals as lookup and Text as output.

[DonKampfello]

Dear Excel-experts,

I am trying write an Index & Match formula to output what kind of wooden box I need in order to ship different pipe variants to our costumers.
The boxes has been designed to carry different pipe types with various dimensions. For now I am only interested in Type and Length, and my goal is to find the right Box with the formula output: T2, T3, T4, T5, etc, etc.

The intervals in the yellow data-area represents optimal max/min lengths in meter, which has been calculated from a weight-to-length ratio. Not sure if this is of any importance, but now you know.

It should be a relatively easy function, however, since my Length data-area is formatted into intervals, it does not seem to work for me and I am not sure whether I am using the Index & Match formula correctly.

I am writing this in the Danish Excel language, but I have translated the function which should be this one. (I am sorry for the inconvenience).

=INDEX($E$9:$O$1127;XMATCH($G5;$E$8:$O$8;0;1);MATCH($F5;$D$9:$D$1127;0))

Link to my worksheet if needed:

Mappe.xlsx

1drv.ms

I hope you can help me. Thanks!

 

[Fluff]

If you are happy to change the values in row 9 how about

Mappe.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3
4					GT	Type	Lenght	=	Box
5		GT	4		4	180	30		T2
6
7
8				Type	Lenght Intervals
9					0	46	91	126	141	201	251	261	301	401	521
10				180	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
11				181	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
12				182	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
13				183	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
14				184	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
15				185	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
16				186	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
17				187	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
18				188	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
19				189	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
20				190	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
21				191	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
22				192	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
23				193	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
24				194	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
25				195	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
26				196	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
27				197	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
28				198	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
29				199	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
30				200	T2	T2	T3	T3	T3	T3	T4	T4	T4	T4	T4
Ark1

Cell Formulas
Range	Formula
I5	I5	=INDEX($E$10:$O$1127,MATCH($F5,$D$10:$D$1127,0),XMATCH($G5,$E$9:$O$9,-1))

 

[DonKampfello]

Thank you very much. Seems to work.

 

[Fluff]

You're welcome & thanks for the feedback.