Look up range and return value

[mthead]

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.

 

[JLGWhiz]

You could test if column A is less than 20 And column B is greater than
twenty, if true D = C.

=IF(AND(A1<20,B1>20),C1,"")

Put in col D and copy down

 

[ker_01]

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

 

[JLGWhiz]

You could use code like this, but this does not tell you if col B is greater
than col A and vice versa. It just tells you that one of them is greater
than 20, so 20 would lie somewhere in between the two numbers.

Sub dk()
Dim lr As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lr
If Cells(i, 1) > 20 Or Cells(i, 2) > 20 Then
MsgBox Cells(i, 3).Value
End If
Next
End Sub

 

[mthead]

Although I didn't think this was going to work, it did perfectly. However,
when I copied the formula to another cell and changed the return column to
return a different value, it returns the false option instead. I checked the
formula 3 times. Any ideas? Here are the two formulas, the first one
worked, the second one did not.

=IF(AND(Sheet1!$I$13>=FreightFactors!$B$3:$B$102,Sheet1!$I$13<=FreightFactors!$C$3:$C$102),FreightFactors!$A$3:$A$102,"")

=IF(AND(Sheet1!$I$13>=FreightFactors!$B$3:$B$102,Sheet1!$I$13<=FreightFactors!$C$3:$C$102),FreightFactors!E3:E102,"")

The only difference is the value_if_true has changed to a different column.

 

[mthead]

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?