Search for value in a range

[JC]

I have a value in cell A1 on Worksheet1 that I want to
check to see if it present in column A on Worksheet2.
For instance. Cell A1 = 123 on worksheet1. I want to see
if 123 is present in a list of numbers in column A of a
different worksheet (worksheet2). If it is present in
worksheet 2, I want to copy the value of the cell to the
right of the found cell in worksheet2 to cell A1 of
worksheet3. In other words, when I find the number 123
in column A on worksheet 2, copy the value of the cell
next to it to worksheet3.

I hope that isn't too confusing.
Regards,
JC

 

[Tom Ogilvy]

Put this in Sheet3, cell A1
=if(countif(Sheet2!A:A,Sheet1!A1)>0,Vlookup(Sheet1!A1,Sheet2!A:B,2,False),""
)

or in code

set rng1 = Worksheets("Sheet1").Range("A1")
set rng2 = Worksheets("Sheet2").Columns(1).Resize(,2)
set rng3 = Worksheets("Sheet3").Range("A1")
if application.Countif(rng2,rng1) > 0 then
rng3value = Application.Vlookup(rng1,rng2,2,false)
End if