Look up range and return value
in [Programming]
|
From: mthead on 1 Apr 2010 18:44 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.
From: JLGWhiz on 1 Apr 2010 19:02 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 "mthead" <mthead(a)discussions.microsoft.com> wrote in message news:0C30E58F-278E-4ED7-A6C6-0C6E105EA41E(a)microsoft.com... > 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.
From: ker_01 on 1 Apr 2010 19:28 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.
From: JLGWhiz on 1 Apr 2010 19:30 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" <mthead(a)discussions.microsoft.com> wrote in message news:0C30E58F-278E-4ED7-A6C6-0C6E105EA41E(a)microsoft.com... > 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.
From: mthead on 2 Apr 2010 15:25
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. "JLGWhiz" wrote: > 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 > > > "mthead" <mthead(a)discussions.microsoft.com> wrote in message > news:0C30E58F-278E-4ED7-A6C6-0C6E105EA41E(a)microsoft.com... > > 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. > > > . > |