It's just like the =vlookup() formula in a cell:

=vlookup(a1,itemlist!b:b,1,false)

(although, if you're just checking for a match, =match() seems more fitting.)
=match(a1,itemlist!b1:b1000,0)

In code:

dim res as variant
res = application.match(myItem,worksheets("itemlist").ra nge("b:b"),0)

if iserror(res) then
msgbox "not found"
else
msgbox "found at pos: " & res
end if

(Any reason not to use the whole column?)

skuzapo wrote:

Hi JMB - thanks for that,

So if the data is in a worksheet and there are over 10000 lines to test
I need to use a range.

How do I do that? Do I need to include the variable at the end which in
this case is called "Item"?

x=Application.VLookup(worksheets("itemlist").range ("b1:b10000"),Item)

--
skuzapo
------------------------------------------------------------------------
skuzapo's Profile: http://www.excelforum.com/member.php...o&userid=27430
View this thread: http://www.excelforum.com/showthread...hreadid=470110

--

Dave Peterson