Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look up range and return value
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look up range and return value
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,B120),C1,"") Put in col D and copy down "mthead" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look up range and return value
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look up range and return value
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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look up range and return value
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,S heet1!$I$13<=FreightFactors!$C$3:$C$102),FreightFa ctors!$A$3:$A$102,"") =IF(AND(Sheet1!$I$13=FreightFactors!$B$3:$B$102,S heet1!$I$13<=FreightFactors!$C$3:$C$102),FreightFa ctors!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,B120),C1,"") Put in col D and copy down "mthead" wrote in message ... 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. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look up range and return value
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to return a range | Excel Programming | |||
Return date if in range, else return blank | Excel Worksheet Functions | |||
drop first row in range and return a new range object | Excel Programming | |||
Return Range Address from Active Range | Excel Programming | |||
Return Range in VB | Excel Programming |