Solved: VLOOKUP

[zoom38]

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
[vba]
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
[/vba]
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)))
[vba]
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
[/vba]

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

[Shazam]

Change in your formula from TRUE to zero and you will get the correct result.



=IF($D33="","",IF($D33="-","-",VLOOKUP($D8,$FY$7:$FZ$31,2,0)))

[zoom38]

Thankyou almighty Shazam that did the trick.

Gary

[geekgirlau]

BTW, the last parameter (that you have just changed) determines whether Excel looks for an exact match, or the closest match. If you want to ensure that only an exact match is found, you must set the last parameter to FALSE or 0.

[zoom38]

Thankyou Geekgirl for the explanation, I was not aware of that. I just took it for granted when it worked.