# vlookup from a formula cell

#### taz23340

##### Active Member
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?

taz

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### Scott Huish

##### MrExcel MVP
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

##### Legend
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

##### Active Member
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

##### MrExcel MVP
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.

Replies
2
Views
305
Replies
4
Views
201
Replies
1
Views
215
Replies
3
Views
392
Replies
4
Views
221

1,181,739
Messages
5,931,759
Members
436,803
Latest member
Strycure12

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back