zoom38
04-27-2006, 11:42 AM
I'm using vlookup data table but am having a slight problem. The data that I am comparing is "V.5" up to "V12" in .5 increments. The problem lies when the formula looks up the following values:
Lookup Value, Formula Result, Desired Result
V9.5 12 9.5
V10 1.5 10
V10.5 1.5 10.5
V11 1.5 11
V11.5 1.5 11.5
V12 1.5 12
Below is a sample formula and the lookup table that is in FY7:FZ31.
=IF($D33="","",IF($D33="-","-",VLOOKUP $D8,$FY$7:$FZ$31,2,TRUE)))
V.5 .5
V1 1
V1.5 1.5
V2 2
V2.5 2.5
V3 3
V3.5 3.5
V4 4
V4.5 4.5
V5 5
V5.5 5.5
V6 6
V6.5 6.5
V7 7
V7.5 7.5
V8 8
V8.5 8.5
V9 9
V9.5 9.5
V10 10
V10.5 10.5
V11 11
V11.5 11.5
V12 12
Is there a way to correct how the formula looks at the data? I tried formating the input cell as text and column FY but that didn't work.
Thanks
Gary
Lookup Value, Formula Result, Desired Result
V9.5 12 9.5
V10 1.5 10
V10.5 1.5 10.5
V11 1.5 11
V11.5 1.5 11.5
V12 1.5 12
Below is a sample formula and the lookup table that is in FY7:FZ31.
=IF($D33="","",IF($D33="-","-",VLOOKUP $D8,$FY$7:$FZ$31,2,TRUE)))
V.5 .5
V1 1
V1.5 1.5
V2 2
V2.5 2.5
V3 3
V3.5 3.5
V4 4
V4.5 4.5
V5 5
V5.5 5.5
V6 6
V6.5 6.5
V7 7
V7.5 7.5
V8 8
V8.5 8.5
V9 9
V9.5 9.5
V10 10
V10.5 10.5
V11 11
V11.5 11.5
V12 12
Is there a way to correct how the formula looks at the data? I tried formating the input cell as text and column FY but that didn't work.
Thanks
Gary