Lookup Help

[tiomin]

Table: Top Row Size, Column A Distance, Data is Volume in m3

Known: Volume(m3) and Distance(m)
Need to solve for Size

Example 1 if Distance was 14m and Volume was 500m3 then size is 1"
Example 2 if Distance was 22m and Volume was 2250m3 then size is 2"


-------------------------Size

Distance---3/4"--------1"---------- 1.25"-------2"
7.6--------- 351---------634-------- 1192------- 2888
15----------266-------- 490---------960-------- 2469
30.5--------193-------- 360-------- 725-------- 1982
46----------156-------- 295-------- 603-------- 1682
61----------133-------- 249-------- 524---------1481

Any way I can do this evaluation in a macro or with functions such as Match, Index, Vlookup, or Hlookup or a combination of two or more functions?

Thanks

 

[Oaktree]

I'm not sure how to read your table. Are 7.6, 15, ..., 61 the lower bound for each distance bucket {e.g. the first bucket would be all distances from [7.6 through 15)}?

What is the Volume range for the 1" bucket in the [7.6-15) distance row?

What happens if distance is less than 7.6 or greater than 61? Or if the volume in the [7.6-15) distance row is less than 351 or greater than 2888?

 

[tiomin]

Your questions

Your reading of the table is correct. This is only a small sample of the table but for all intend and purpose, anything outside of distance, volume and size will error to "out of range".

Hope this helps.

 

[tiomin]

Dis -------- 3/4"----------1"-----------1.25"-------2"
7.6--------- 351---------634-------- 1192------- 2888
15----------266-------- 490---------960-------- 2469
30.5--------193-------- 360-------- 725-------- 1982
46----------156-------- 295-------- 603-------- 1682
61----------133-------- 249-------- 524---------1481

Volume (column) for the 1" bucket are: 634,490,360,295,249
Volume (column) for the 1.25" bucket are: 1192,960,725,603,524
Volume (column) for the 2" bucket are: 2888,2469,1982,1682,1481

Distance column values are: 7.6, 15, 30.5, 46, 61

Distance (meters) would be 0-7.6, 7.7-15, 15.1-30.5, 30.6-46, 46.1-61.

In this example any distance over 61 meters would be out of range.
Volume above or below each column would also be considered as out of range. Therefore Size will also be out of range when either Volume or Distance is out of range.

Thanks

 

[Aladin Akyurek]

Dis -------- 3/4"----------1"-----------1.25"-------2"
7.6--------- 351---------634-------- 1192------- 2888
15----------266-------- 490---------960-------- 2469
30.5--------193-------- 360-------- 725-------- 1982
46----------156-------- 295-------- 603-------- 1682
61----------133-------- 249-------- 524---------1481

Volume (column) for the 1" bucket are: 634,490,360,295,249
Volume (column) for the 1.25" bucket are: 1192,960,725,603,524
Volume (column) for the 2" bucket are: 2888,2469,1982,1682,1481

Distance column values are: 7.6, 15, 30.5, 46, 61

Distance (meters) would be 0-7.6, 7.7-15, 15.1-30.5, 30.6-46, 46.1-61.

In this example any distance over 61 meters would be out of range.
Volume above or below each column would also be considered as out of range. Therefore Size will also be out of range when either Volume or Distance is out of range.

Thanks

Book5
	A	B	C	D	E	F	G	H	I	J
1		Size
2	Dis	3/4"	1"	1.25"	2"			Dis	Vol	Size
3	7.6	351	634	1192	2888			14	500	1"
4	15	266	490	960	2469			22	2250	2"
5	30.5	193	360	725	1982
6	46	156	295	603	1682
7	61	133	249	524	1481
8
Sheet1

J3, copied down:

=INDEX($B$2:$E$2,MATCH(I3,INDEX($B$3:$E$7,MATCH(H3,$A$3:$A$7,1),0),1)+(LOOKUP(I3,INDEX($B$3:$E$7,MATCH(H3,$A$3:$A$7,1),0))<>I3))

 

[tiomin]

Great solution. Thanks very much!

There is a slight problem. Using the example from above, if we enter a distance of 15m and a volume of 266 m3 then the Size comes up as 3/4" which is correct. However, if we change the volume to 265 m3 then the answer comes up as #NA. Any idea of how to correct this?

The same hold true if we input 14m and volume of 266 m3 the answer comes out as #NA.

In both cases it should still pick 3/4" as the answer because in the first case 265 m3 can be supplied by the 3/4" pipe for a distance of 15m (if it can supply 266 m3 for 15m). In the second case if 15m can supply 266 m3 then 14m can supply 266 m3.

Logic: the longer the distance the less volume it can supply and the smaller the diameter the less volume it can supply.

Any idea of how we can solve this?

 

[Aladin Akyurek]

Great solution. Thanks very much!

There is a slight problem. Using the example from above, if we enter a distance of 15m and a volume of 266 m3 then the Size comes up as 3/4" which is correct. However, if we change the volume to 265 m3 then the answer comes up as #NA. Any idea of how to correct this?

The same hold true if we input 14m and volume of 266 m3 the answer comes out as #NA.

In both cases it should still pick 3/4" as the answer because in the first case 265 m3 can be supplied by the 3/4" pipe for a distance of 15m (if it can supply 266 m3 for 15m). In the second case if 15m can supply 266 m3 then 14m can supply 266 m3.

Logic: the longer the distance the less volume it can supply and the smaller the diameter the less volume it can supply.

Any idea of how we can solve this?

aaIndexMatchLookup tiomin.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1		Size
2	Dis	3/4"	1"	1.25"	2"			Dis	Vol	Size	Calc Dis	Calc Rec
3	7.6	351	634	1192	2888			14	500	1"	2	2
4	15	266	490	960	2469			22	2250	2"	3	4
5	30.5	193	360	725	1982			15	266	3/4"	2	1
6	46	156	295	603	1682			15	265	3/4"	2	1
7	61	133	249	524	1481			14	266	3/4"	2	1
8
Sheet1

J3:

=INDEX($B$2:$E$2,L3)

K3:

=MATCH(H3,$A$3:$A$7,1)+(LOOKUP(H3,$A$3:$A$7)<>H3)

L3:

=MATCH(IF(ABS(I3-MIN(INDEX($B$3:$E$7,K3,0)))<=15,MIN(INDEX($B$3:$E$7,K3,0)),I3),INDEX($B$3:$E$7,K3,0),1)

Adjust the constant of 15 to suit.

 

[tiomin]

Thanks so much for your solution. It works very well. Sorry for asking this dumb question, but what does the constant do and why did you pick 15?

 

[Aladin Akyurek]

Thanks so much for your solution. It works very well.

You are welcome.

Sorry for asking this dumb question, but what does the constant do and why did you pick 15?

If a volume to be looked up is smaller than any value in the column headed with 3/4", that lookup value is reset (recalculated) to an appropriate value in that column. For example:

340 --> 351 for 7.6 for Abs(340-351) <= 15.

So you might want to adjust 15 to a more reasonable value.