Index, Lookup, Match?

[dougwashere]

Hey There,

I am hoping someone can help me out here.

I am looking for a formula that will choose the "Closest Match" from the "Available On Hand" list based on the three "Calculated" columns. It does not need to be an exact match, but must fall between the "Calculated MIN and MAX". And have it continue choosing the closest but not using more options than what is on hand.

Basically I want to make sure I am using the best option and all the options, before I have to order additional shims.

Any help would be appreciated.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
3					MIN	MID	MAX			Calculated New Clearance (mm)
4		Exhaust Spec (mm)			0.25	0.30	0.35
5		Measured (inch)	Measured (mm)	Current Shim	Calculated MIN Shim	Calculated MID Shim	Calculated MAX Shim	Closest Match On Hand	Shim Installed			Available Shims On Hand
6
7
8	Cylinder	0.005	0.127	5.450	5.327	5.277	5.227		5.450	0.127		5.530
9	1	0.008	0.203	5.450	5.403	5.353	5.303		5.450	0.203		5.490
10												5.490
11	Cylinder	0.009	0.229	5.430	5.409	5.359	5.309		5.450	0.209		5.470
12	2	0.009	0.229	5.420	5.399	5.349	5.299		5.420	0.229		5.460
13												5.450
14	Cylinder	0.006	0.152	5.410	5.312	5.262	5.212		5.410	0.152		5.450
15	3	0.011	0.279	5.390	5.419	5.369	5.319		5.390	0.279		5.450
16												5.450
17	Cylinder	0.011	0.279	5.390	5.419	5.369	5.319		5.390	0.279		5.450
18	4	0.010	0.254	5.340	5.344	5.294	5.244		5.340	0.254		5.430
19												5.420
20	Cylinder	0.000	0.000	0.000	-0.250	-0.300	-0.350		0.000	0.000		5.410
21	5	0.000	0.000	0.000	-0.250	-0.300	-0.350		0.000	0.000		5.390
22												5.390
23	Cylinder	0.000	0.000	0.000	-0.250	-0.300	-0.350		0.000	0.000		5.340
24	6	0.000	0.000	0.000	-0.250	-0.300	-0.350		0.000	0.000
Sheet2

Cell Formulas
Range	Formula
E8	=SUM($C8,-($E$4-$D8))
E9	=SUM($C9,-($E$4-$D9))
E11	=SUM($C11,-($E$4-$D11))
E12	=SUM($C12,-($E$4-$D12))
E14	=SUM($C14,-($E$4-$D14))
E15	=SUM($C15,-($E$4-$D15))
E17	=SUM($C17,-($E$4-$D17))
E18	=SUM($C18,-($E$4-$D18))
E20	=SUM($C20,-($E$4-$D20))
E21	=SUM($C21,-($E$4-$D21))
E23	=SUM($C23,-($E$4-$D23))
E24	=SUM($C24,-($E$4-$D24))
F8	=SUM($C8,-($F$4-$D8))
F9	=SUM($C9,-($F$4-$D9))
F11	=SUM($C11,-($F$4-$D11))
F12	=SUM($C12,-($F$4-$D12))
F14	=SUM($C14,-($F$4-$D14))
F15	=SUM($C15,-($F$4-$D15))
F17	=SUM($C17,-($F$4-$D17))
F18	=SUM($C18,-($F$4-$D18))
F20	=SUM($C20,-($F$4-$D20))
F21	=SUM($C21,-($F$4-$D21))
F23	=SUM($C23,-($F$4-$D23))
F24	=SUM($C24,-($F$4-$D24))
G8	=SUM($C8,-($G$4-$D8))
G9	=SUM($C9,-($G$4-$D9))
G11	=SUM($C11,-($G$4-$D11))
G12	=SUM($C12,-($G$4-$D12))
G14	=SUM($C14,-($G$4-$D14))
G15	=SUM($C15,-($G$4-$D15))
G17	=SUM($C17,-($G$4-$D17))
G18	=SUM($C18,-($G$4-$D18))
G20	=SUM($C20,-($G$4-$D20))
G21	=SUM($C21,-($G$4-$D21))
G23	=SUM($C23,-($G$4-$D23))
G24	=SUM($C24,-($G$4-$D24))
C8	=B8*25.4
C9	=B9*25.4
C11	=B11*25.4
C12	=B12*25.4
C14	=B14*25.4
C15	=B15*25.4
C17	=B17*25.4
C18	=B18*25.4
C20	=B20*25.4
C21	=B21*25.4
C23	=B23*25.4
C24	=B24*25.4
J8	=$C8+($D8-$I8)
J9	=$C9+($D9-$I9)
J11	=$C11+($D11-$I11)
J12	=$C12+($D12-$I12)
J14	=$C14+($D14-$I14)
J15	=$C15+($D15-$I15)
J17	=$C17+($D17-$I17)
J18	=$C18+($D18-$I18)
J20	=$C20+($D20-$I20)
J21	=$C21+($D21-$I21)
J23	=$C23+($D23-$I23)
J24	=$C24+($D24-$I24)

 

[Misca]

Welcome to the message board!

If you want to use approximate matches your lookup column needs to be sorted. All the approximate searches are basically running down the column 'till they bump into first value that's bigger than what they're looking for and then take one step back. That's not exactly how they work but that's the general idea.

 

[dougwashere]

Thank you for that. But I'm not sure how to create the formula.

 

[Misca]

Here's my suggestion using two additional columns to sort the current shims on hand column:

Ranking formula to M8:M23:
=RANK(L8,$L$8:$L$23,1)+COUNTIF($L$3:$L8,L8)-1


This one ranks the shims in ascending order. If there are more than one same values in the list the RANK returns the same ranking for each of them. I'm using COUNTIF -1 to solve this problem. Just make sure the COUNTIF is using expanding range.


Sorted Available Shims On Hand column to N8:N23:
=INDEX($L$8:$L$23,MATCH(ROWS($N$8:N8),$M$8:$M$23,0))


This one returns the shims from L-column in ascending order. The MATCH formula is looking for the exact match for the expanding row number from the rank column.


The actual lookup formula looks for approximate match for the E8 value from the sorted shims column:
=INDEX($N$8:$N$23,MATCH(E8,$N$8:$N$23))

Hope is solves your problem and happy holidays!