Index and Match with data in intervals (length and width)

[dennistran]

Hi forum,

I'm new to this forum and relatively new to Excel and its functions. I have an Excel problem that I cannot solve easily by myself. I've tried googling and YouTubing without any progression.

I work as a controller at a company in the Stone Industry and I have the responsibility to price our inventory. Now, the price of the stone is depending on the guaranteed length and width that the customer can use in his production. Therefore, there is a matrix of length and width with respectively price, see picture.

Picture 1: https://ibb.co/iA7P96


Now I want to lookup these values according to picture 2: https://ibb.co/cq7wNR

I will use the top row as an example. Where L = 195 and W = 184
The logic is to first determine the guaranteed length of the block and it is over 191-
Thereafter, we determine the width which is over 101-
This returns the value 47 000.

Can you please help me with the logical function of this problem?

I can post the workbook if you want.



Kind regards,
Dennis Tran

 

[edmitchell]

Dennis - Hi

I've entered the values in picture 1 into a sheet called 'Lookup' and added 1 column and 1 row, both of which show the minimum Length/Width for that range, e.g.

[TABLE="width: 448"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Length[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Width[/TD]
[TD]-70[/TD]
[TD]71-100[/TD]
[TD]101-130[/TD]
[TD]131-190[/TD]
[TD]191-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Min [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]191[/TD]
[/TR]
[TR]
[TD]-50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]22800[/TD]
[TD="align: right"]23800[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]33200[/TD]
[TD="align: right"]38400[/TD]
[/TR]
[TR]
[TD]50-70[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]27400[/TD]
[TD="align: right"]31700[/TD]
[TD="align: right"]35100[/TD]
[TD="align: right"]37000[/TD]
[TD="align: right"]40400[/TD]
[/TR]
[TR]
[TD]71-100[/TD]
[TD="align: right"]71[/TD]
[TD][/TD]
[TD="align: right"]24400[/TD]
[TD="align: right"]39800[/TD]
[TD="align: right"]42300[/TD]
[TD="align: right"]45000[/TD]
[/TR]
[TR]
[TD]101-[/TD]
[TD="align: right"]101[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]40100[/TD]
[TD="align: right"]44550[/TD]
[TD="align: right"]47000[/TD]
[/TR]
</tbody><colgroup><col span="7"></colgroup>[/TABLE]


(which I hope shows in 'Bold'!)- obviously you could hide the extra row/column if you don't need to see them!

then the formula to get the corresponding value becomes:
=INDEX(Lookup!C4:G7,MATCH(B2,Lookup!B4:B7,1),MATCH(B1,Lookup!C3:G3,1))

where 'B2' holds the length you are looking up, and 'B1' holds the width

I've tried various sizes and they all appear to work!

Ed

 

[dennistran]

Wow, never readly thought about it but now when I think about it, it really feels so logical! So the ending 1 in the Match function returns less?

I don’t have my worksheet with me right now but come Monday I will definitely try this out.

Thanks Ed.

 

[wideboydixon]

If you can't / don't want to add another row/column then you could reformat the existing entries and do something like this:

Book1
	A	B	C	D	E	F
1		Length
2	Width	000-070	071-100	101-130	131-190	191-
3	000-050	22800	23800	25000	33200	38400
4	050-070	27400	31700	35100	37000	40400
5	071-100		24400	39800	42300	45000
6	101-			40100	44500	47000
7
8
9	L	W	Lookup
10	195	184	47000
11	74	69	31700
12	103	69	35100
13	75	57	31700
Sheet1

Cell Formulas
Range	Formula
C10	=INDEX($B$3:$F$6,MATCH(TEXT($B10,"000-Z"),$A$3:$A$6,1),MATCH(TEXT($A10,"000-Z"),$B$2:$F$2,1))

WBD

 

[dennistran]

I’m not so familiar with the text function, could you please explain the logic?