lookup single value in entire worksheet

[jasonbennett07]

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!!!

 

[Guest]

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

 

[jasonbennett07]

You are the man. Or woman. Whichever.

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