This looks basically like what I want to do, except I need to do it multiple
times at once. I'll try to explain.

I have a weight in cell Sheet1 D11

Sheet2 is a table. Columns A & B are min and max values for a range of
weights. So A1 is 0, B1 is 2.5. A2 is 2.6, B2 is 5. On and on. Column C
is a dollar amount based on that weight. So for row 1 (range 0 - 2.5) the
charge is $1.00. Column D is another dollar amount for each range, so row 1
the charge is $1.50. This repeats for Columns E, F, G. I have 5 zones for
each weight range.

Back on Sheet1 I again have 5 zones. I want the freight cell for each zone
to refer to the table on Sheet2 and extract the appropriate charge (Columns
C, D, E, F, or G) based on the weight indicated in cell D11.

Does that make sense?


"ker_01" wrote:

mthead-

Here is a framework to get you started- feel free to post back if you have
trouble adapting this to your needs (and see comments below).

1. You will probably find it easier to either put your reference range in
the same workbook, or if that isn't an option, at least have the workbook
containing the reference range open anytime you open a workbook where you
need to calculate these values. This code hardcodes your source range for
convenience.

2. This code assumes that you will be using numeric values only for your
comparisons. If you have any numbers stored as text, etc, expect it to return
an error.

3. You didn't specify what you wanted to do with overlapping ranges, so I
assumed which case would be = vs <=. Feel free to change that if appropriate.

Open the VBE, copy/paste this into a new module. Then in your worksheet, you
can use it just like any other formula
=ReturnNewValue(A1) where cell A1 would contain your target value.

Code: (watch for linewrap)

Function ReturnNewValue(IValue)

Dim CompareRange As Variant
CompareRange = Sheet1.Range("A1:C150") '<-change this to your actual source
range

For p = LBound(CompareRange) To UBound(CompareRange)
If IValue = CompareRange(p, 1) And IValue < CompareRange(p, 2) Then
ReturnNewValue = CompareRange(p, 3)
Next

End Function

"mthead" wrote:

What I need to do:

Using Value "A" (let's say 20), lookup on on a table and find the row that
20 falls in the range of column A and column B (let's say column A is 15 and
column B is 25) i want to return the value of column C in the same row.
There is a possibility of 150 different rows. Also, value A is in workbook
Jobs, and the table is in workbork Tables.

Any expertise would be appreciated.