# Lookup Help

#### tiomin

##### Board Regular
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

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### Oaktree

##### MrExcel MVP
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

##### Board Regular

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

##### Board Regular
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

##### MrExcel MVP
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
ABCDEFGHIJ
1Size
2Dis3/4"1"1.25"2"DisVolSize
37.635163411922888145001"
41526649096024692222502"
530.51933607251982
6461562956031682
7611332495241481
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

##### Board Regular
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?

##### MrExcel MVP
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
ABCDEFGHIJKL
1Size
2Dis3/4"1"1.25"2"DisVolSizeCalc DisCalc Rec
37.635163411922888145001"22
41526649096024692222502"34
530.51933607251982152663/4"21
6461562956031682152653/4"21
7611332495241481142663/4"21
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

##### Board Regular
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?

##### MrExcel MVP
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.

Replies
3
Views
552
Replies
1
Views
307
Replies
3
Views
482
Replies
31
Views
3K
Replies
3
Views
231

1,190,997
Messages
5,984,068
Members
439,872
Latest member
noaman79

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back