Find Function

[Rolly_Sefu]

Hello,

I have a column of data ( may be 100 rows or 1000 rows ), each cell i need to find it in another workbook, but not every time in the same column.

So i used to take each one; one by one and use CTRL + F to find it in the other workbook.

Is there a possibility to create a function "findcustom" that would do all this automatically ?

=findcustom(A2, '[text.xlsx]text'!$A2:$Z1000, # , @ , %)
# = return info:
- 0 - column name
- 1 - found value
@ = offset ( + or - column )
% = if error ( not found ) - "missing" ( message in cell )

This would be like an upgraded vlookup since it would look in the whole worksheet.

Any ideas ?
Thank you.

[mse330]

Hello,

Try the below UDF & see if it fulfills your need. It has 4 arguments as follows

Search Item = the text you are looking for
Rg (range) = select the range you need to search the word in
Exact Match (optional) = determine whether you need the exact text or partial text you are searching in the cell
Return Address (optional) = Return the cell address (e.g. B7, A1 … etc.) or the text of the found cell

For optional arguments of the function, you could leave or fill with True/False or alternatively 1/0



Function FindCustom(SearchItem As String, Rg As Range, Optional ExactMatch As Boolean, Optional ReturnAddress As Boolean) As String
Dim ReturnValue As Range
Set ReturnValue = IIf(ExactMatch = True, Rg.Find(SearchItem, LookAt:=xlWhole), Rg.Find(SearchItem, LookAt:=xlPart))
If ReturnValue Is Nothing Then
FindCustom = "Not Found"
Else
FindCustom = IIf(ReturnAddress = True, Replace(ReturnValue.Address, "$", ""), ReturnValue.Value)
End If
End Function

[Rolly_Sefu]

Hello,

This works pretty good.
One more question. how can i offset the result + x columns or - x columns ??

Thank you.

[mse330]

I am not really clear on what do you mean by offset the results. So I you used the UDF in cell A1 and the lookup range is B1:C10, the result of the function will be in A1 where you applied the function. Do you need to offset the results of the applied function in A1 into another cell ?

[Rolly_Sefu]

Offset the result like Vlookup(A1,A2:B20,2,0)

Find A1 and return equivalent from column B ( 2 )

In the function be able to do the same if the info is found in col D, and want the result from col F => offset +2

[mse330]

Ok, I see your point. I have added one more optional argument in the function where you have to add the # of columns to be offset or leave black & it will return the same column. Revised UDF below



Function FindCustom(SearchItem As String, Rg As Range, Optional ExactMatch As Boolean, _
Optional ReturnAddress As Boolean, Optional ColOffset As Long) As String
Dim ReturnValue As Range
Set ReturnValue = IIf(ExactMatch = True, Rg.Find(SearchItem, LookAt:=xlWhole), Rg.Find(SearchItem, LookAt:=xlPart))
If ReturnValue Is Nothing Then
FindCustom = "Not Found"
Else
FindCustom = IIf(ReturnAddress = True, Replace(ReturnValue.Offset(, ColOffset).Address, "$", ""), ReturnValue.Offset(, ColOffset).Value)
End If

End Function

[Rolly_Sefu]

Works perfectly.
Thank you.

[mse330]

Glad to help & thanks for the feedback