Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning value based on input data
I am trying to write a formula or VBscript that will accomplish this seemingly simple task: In CELL A I want to enter a value, in this case the price of diesel fuel per gallon. I then want to compare that value against a table that contains 3 columns ABC and return a value base on that comparison, for example: Current Diesel Price = x if x is greater than w but less than y return value z. and if thats false check the next row in the table until true. the lookup table looks like this greater than less than charge % 1.00 1.05 14% I need a single line for entry and a single result line, such as: (user enters this value) D.O.E. PRICE: 2.654 (formula returns this value) FSC% 16.50% I need it to look up the value from a table on another sheet formated as: DOE Price at least less than FSC% 2.31 2.32 14.0 2.32 2.33 14.1 2.33 2.34 14.2 2.34 2.35 14.3 2.35 2.36 14.4 2.36 2.37 14.5 2.37 2.38 14.6 Thanks for any help. -- mtroute ------------------------------------------------------------------------ mtroute's Profile: http://www.excelforum.com/member.php...o&userid=33562 View this thread: http://www.excelforum.com/showthread...hreadid=533483 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning value based on input data
If you can create a helper column on your sheet with the lookup data,
you can do this: Assuming entry in Sheet1, A1 and result in Sheet2, A2, with lookup data in Sheet2 columns A-C and the helper column in D. In Sheet2, column D enter this next to your first FSC% figu =IF(AND(Sheet1!$A$1=Sheet2!A1,Sheet1!$A$1<Sheet2! B1),Sheet2!C1,0) Drag that formula down to your last data row. in Sheet1, A2 enter: =MAX(Sheet2!D1:D10) substituting your helper column range for D1:D10. When you enter a value in A1, the helper column puts zeros in each of its rows save the one meeting your criteria. The formula in A2 simply finds the only nonzero value in the range. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning value based on input data
An addendum: The formula below assumes your first data row is row 1.
If different, the references to Sheet2!A1, Sheet2!B1 and Sheet2!C1 would change (the 1 would change to whichever row number your formula is being entered into). =IF(AND(Sheet1!$A$1=Sheet2!A1,Sheet1!$A$1<Sheet2! B1),Sheet2!C1,0) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return multiple rows of data based on criteria | Excel Worksheet Functions | |||
Clear"Reset" data input. | Excel Worksheet Functions | |||
Pull data from another sheet based on certain criteria | Excel Discussion (Misc queries) | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
populating sheets based on data from parent sheets | Excel Discussion (Misc queries) |