lookup single value in entire worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jasonbennett07@yahoo.com - 13 Apr 2006 17:59 GMT

I have a single list of values (we'll call it A:A) that I need to find
where they exist in another worksheet (Sheet2A:IV).

Obviously, if I do a Vlookup (or Hlookup), it'll only look in row A on
Sheet2 for my specified value. Is there a formula that will look in
all cells, in Sheet2, for a specific record in Sheet1?

Thanks for your help!!!

Reply to this Message

Gary''s Student - 13 Apr 2006 19:36 GMT

Try this little UDF:

Function where_is(r As Range) As String
Dim r2, r3 As Range
where_is = ""
v = r.Value
Set r2 = Worksheets("Sheet2").Range("A1:IV65536")
For Each r3 In r2
If r3.Value = v Then
where_is = r3.Address
Exit Function
End If
Next
End Function

Signature

Gary's Student

> I have a single list of values (we'll call it A:A) that I need to find
> where they exist in another worksheet (Sheet2A:IV).
[quoted text clipped - 4 lines]
>
> Thanks for your help!!!

Reply to this Message

jasonbennett07@yahoo.com - 13 Apr 2006 20:18 GMT

You are the man. Or woman. Whichever.

That is really cool, I need to learn how to do this stuff.

Reply to this Message