On Thu, 9 Jun 2011 20:43:28 +1000, "Vacuum Sealed" wrote:

Hi All

I would like to implement a value lookup from a range using VBA, not a cell
formula.

Was toying with something like:

for I = 6 to 3000
for J = 6 to 3000

Sheets("MAIN DATA").Range("AG" & i).Value = LOOKUP( 'SUPPORT
DATA'!$D$11:$J$1000,'SUPPORT DATA'!$G$11:$G$1000, 'MAIN DATA'.Range("I" &
J))

Sheets("MAIN DATA").Range("AH" & i).Value = LOOKUP( 'SUPPORT
DATA'!$D$11:$J$1000,'SUPPORT DATA'!$H$11:$H$1000, 'MAIN DATA'.Range("I" &
J))

Next I
Next J

Of course, I am acutely aware the syntax is way wrong, just hoping someone
can rearrange it so it can make sense..

Thanks heaps

Mick.



I'm not sure what values you are looking up, but you can just use the lookup member of the worksheetfunction class:

e.g:

YourVariable = worksheetfunction.lookup(lookup_value, lookup_vector, result_vector)

or

YourVariable = worksheetfunction.lookup(lookup_value, array)

lookup_value can be a single variable or cell reference.
array or lookup_vector & result_vector can be range references