In say, Sheet1,

Try something like this in B1:
=IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,0)),0 ,
IF(VLOOKUP(A1,Sheet2!A:B,2,0)30,RANDBETWEEN(20,30 ),A1)))

where A1 will hold an input to lookup

If A2, A3, etc holds yet other inputs,
the formula in B1 can be copied down "as-is" to return corresponding values
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AussieExcelUser"
<AussieExcelUser.24fg6q_1141949487.7215@excelfor um-nospam.com wrote in
message news:AussieExcelUser.24fg6q_1141949487.7215@excelf orum-nospam.com...

I need to apply the "RANDBETWEEN" function to a formula result in a cell
and am having difficulty. I can get the following formula to work by
using the "Iteration" function set to 1 but get erratic results,
including values added to cells where there were no values as a result
of the Lookup:


"=if(if(isna(vlookup($A$1,sheet2!A$1$:B$1,2,false) ),"0",vlookup($A$1,sheet2!
A$1$:$B$1,2,false))30,RANDBETWEEN(20,30),A1)"

What I want to do is generate a random number between 20 and 30 if the
result of the lookup is greater than 30. The lookup can result in
values from negative 500 through to postive 500.

Any help would be appreciated as this is driving me nuts and costing a
lot of development time.


--
AussieExcelUser
------------------------------------------------------------------------
AussieExcelUser's Profile:
http://www.excelforum.com/member.php...o&userid=32326
View this thread: http://www.excelforum.com/showthread...hreadid=520848