Excel using Vlookup to match a percentage?

[Tony1l]

I've have a strange problem. I have two sheets. 1 cell that has this formula to determine percentages and is formatted as a percentage.
=IF(H3>0,(((H2-H3)/H2)),100%)
..this correctly displays percentages in the cell such as 99.90%
Now My search formula looks like this...
=VLOOKUP(H9,Table!$A$2:$B$12,2,FALSE)
And my 2nd sheet named (Table) has a two column list. The first is percentages and the 2nd is the return value, example:
99.90% 1.00
99.91% 2.00
99.92& 3.00
The real problem is the formula above is creating amounts equal to the percentages on the second sheet such as 99.90% but it never matches. I've printed the length of the cell and even though =IF(H3>0,(((H2-H3)/H2)),100%) sometimes the values displayed are exact matches, such as 99.90% but its length is 17. I discovered that it's calculating this percentage from the value 0.999334455577655 or similar and displays the correct 99.90% in the cell, but when it compares this value to my second sheet it's trying to match the 0.999334455577655 instead of the displayed value of the percent formatted cell 99.90%. I know this because I changed my table value from 99.90% to the above number and it then matched.
My question is...Is there a way to format a cell as a percentage and lookup the returned percentage with a table on a second sheet all within the same cell?

 

[kpetursson]

do you need the percise number generated by the first formula?

if not the you could add a round function to the equation to round it to four decimal places. The values should the be the same.
=round(cell,decimal_places)

hope this works for you.

Note lookup in the help for the vlookup function. I know you can have it do the lookup so that it will pick the next highest/lowest number if it can't find an exact match.



Kevin Petursson

 

[SilentAiche]

Kevin & Tony:

Would checking the box for "Precision as Displayed" under Tools / Options / Calculation (tab) make a difference?

Tim

 

[SkipVought]

The VALUE that you calculate is not necessarily EXACT. It may DISPLAY exactly how you want to see it, but if you were to format the column to display more places to the right of the decimal, you'ld probably see that the values are not EXACTLY what you expect.

So here's what to do. Use the MATCH function to lookup your values within the INDEX fucntion to return a value
[tt]
=index(Table!$B$2:$B$12,match(H9,Table!$A$2:$A$12,1),1)
[/tt]
you may need to "adjust" the threshhold values in your table.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[kpetursson]

if the table your looking up in has the first column sorted in accending value, you can set the last term of the lookup up function to TRUE.
ie. vlookup(vlaue,lookup_range, column, TRUE)
Then the lookup function will choose the largest value that is equal or lesser then the lookup value.

note: your first value should be 0 (since your not likely to get a negative value from the sound of it). This will prevent a lookup error. If the lookup value is smaller than the smallest value in the lookup table you will get an error.

All of this is found in the help menu of excel. I looked up "vlookup".

Hope you find this useful.



Kevin Petursson