bsmiller92
New Member
- Joined
- Mar 21, 2014
- Messages
- 7
I am currently working on a worksheet for our Construction Department. The worksheet has a Plan Quantity column, a $ per Unit column, and a Final Quantity column. These 3 columns are the cells where the data gets entered. The next column, Difference, calculates the % difference between the Plan Qty. and the final Qty. (this column can be a positive or negative value). The next column is Factor, followed by the Adjusted $ amount, and the final column is the lookup value (this column rounds down the Difference column and is two decimal number formatted, the same as the lookup indexes). There is worksheet tab called T2 that carries the negative number index (-1.00 to -100.00) with the 2nd column being the Factor value for the VLOOKUP, and the 2nd worksheet, T3, carries the positive number index (0.00 to 400.00). Here is the formula for the Factor lookup:
=IF(J7<0,VLOOKUP(J7,'T2'!$A$1:$B$100,2,0),VLOOKUP(J7,'T3'!$A$1:$B$401,2,0))
This lookup works for all 55 rows except 4 of them. If the [range_lookup] is changed from 0 to 1, the 4 rows that didn’t work, do work, then others don’t.
This worksheet will be a template for all future Construction projects, so this needs to be able to always work.
Leading and trailing spaces do not exist - both index lookups have been properly sorted - all cells are properly formatted.
MS Office Excel 2010, Windows 7 Pro
bsmiller92
=IF(J7<0,VLOOKUP(J7,'T2'!$A$1:$B$100,2,0),VLOOKUP(J7,'T3'!$A$1:$B$401,2,0))
This lookup works for all 55 rows except 4 of them. If the [range_lookup] is changed from 0 to 1, the 4 rows that didn’t work, do work, then others don’t.
This worksheet will be a template for all future Construction projects, so this needs to be able to always work.
Leading and trailing spaces do not exist - both index lookups have been properly sorted - all cells are properly formatted.
MS Office Excel 2010, Windows 7 Pro
bsmiller92
Last edited:
