vlookup from a formula cell

taz23340

Active Member
Joined
Jun 11, 2003
Messages
336
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Sep 3, 2002
Messages
10,473
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
Joined
Jun 11, 2003
Messages
336
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
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,147,619
Messages
5,742,175
Members
423,708
Latest member
vagosh2001

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top