vlookup from a formula cell

[taz23340]

hi guys,
you've all been helpful before with vlookup and i have come into another snag. probably just me though. i am trying to use vlookup and it all works fine it's a easy one, very simple =VLOOKUP(D10,A3:B19,2,FALSE), my problem is the d10, that cell has a formula for random choosing. whe i erase that formula from the cell and keep a real number, i get my vlookup answer. but when i use the formula rand to get me a number the vlookup comes up with n/a, any idea why vlookup doesn't accept to look up from this formula, could it be that it generated decimals and i formated cell to only show whole numbers?

thanks in advance
taz

 

[Scott Huish]

That is probably the problem.

Instead of just formatting the cells to show a whole number, if your lookup table has only whole numbers for lookup values, try modifying your RAND formula with the INT function, such as:

=INT(RAND()*10)

so that you are actually generating a whole number instead of just displaying it that way.

 

[just_jon]

Your RAND formula's generating a number with decimal pieces to it, and you're trying to match it to an integer, I think --

1.0091 <> 1

So, wrap that D10 formula in a ROUND/TRUNC/INT/MOD function.

 

[taz23340]

thanks hot pepper, never heard of the int function but it work like a charm, so does that work with any other formula to prevent decimal points.

 

[Scott Huish]

What INT does on the worksheet is rounds the number down to the nearest integer. So with positive numbers, it will essentially remove the fractional part of the number, leaving only the integer part.

So with a positve number of 8.12, using the INT function would return 8, but with -8.12, it would return -9 because it is rounding down to the nearest integer. If you are using negative numbers or a combination of negative and postive numbers I would look at using the TRUNC function which just chops off the fractional part.