Solved: Alternate for INDEX/MATCH

[vishwakarma]

Hi guys,

Is there a way by which the formula in my attached file can be shorten. OR Is there any alternate but simple function by which we can get the same result.

I'm trying to retrieve "Company Name" on the basis of domain from Data tab of the file. I have the list of Company Names along with the different domains in multiple columns in the this tab.

It is just a sample file I have a workbook which has more than 2000 records like this and when I use this file it always get hanged plus takes lots of time to do the calculation.


Any help appreciated...

Thanks,

[Paul_Hossler]

I usually prefer to use a User Defined Function instead of complicated worksheet formulas. Just my preferance, but here's a UDF and sample workbook if you're interested



Option Explicit
Function FindCompany(sDomain As String, rData As Range) As String
Dim iRow As Long, iCol As Long
FindCompany = vbNullString
With Application.WorksheetFunction
iRow = 0
For iCol = 2 To rData.Columns.Count
On Error Resume Next
iRow = .Match(sDomain, rData.Columns(iCol), 0)
On Error GoTo 0
If iRow > 0 Then
FindCompany = rData.Cells(iRow, 1)
Exit Function
End If
Next iCol
End With
End Function

Sub drv()
MsgBox FindCompany("Not There.com", Worksheets("Data").Range("A:F"))
MsgBox FindCompany("corp.disney.com", Worksheets("Data").Range("A:F"))
MsgBox FindCompany("pxp.com", Worksheets("Data").Range("A:F"))
End Sub


You do need to enable macros to do it this way so that could be an issue

Another way would be to use a Sub that just updates the correct cell on the worksheet with a value, not a formula. That way ther is no re-calculation required, but if the data changes you do need to re-run the sub

Paul

[Kenneth Hobs]

Sub Test()
MsgBox FindData("resmed.com")
End Sub

'=FindData(A2)
Function FindData(url As String) As String
Dim r As Range, f As Range
Application.Volatile False
If url = "" Then Exit Function
Set r = Worksheets("Data").Range("A2").End(xlDown)
Set f = Worksheets("Data").Range("B2:F" & r.Row).Find(url, Lookat:=xlWhole)
If Not f Is Nothing Then _
FindData = Worksheets("Data").Range("A" & f.Row).Value
End Function

[stanleydgrom]

vishwakarma,

Detach/open workbook GetCoName - vishwakarma - VE37090 - SDG13.xlsm and run macro GetCoName.


Or, if you want to try the macro on another workbook:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.




Option Explicit
Sub GetCoName()
' stanleydgrom, 04/18/2011
' http://www.vbaexpress.com/forum/showthread.php?t=37090
Dim c As Range, d As Range, firstaddress As String
Application.ScreenUpdating = False
Worksheets("Formula").Activate
For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
firstaddress = ""
With Worksheets("Data").UsedRange
Set d = .Find(c, LookIn:=xlValues, LookAt:=xlWhole)
If Not d Is Nothing Then
firstaddress = d.Address
Do
c.Offset(, 1) = Worksheets("Data").Range("A" & d.Row)
Exit Do
Set d = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
Next c
Application.ScreenUpdating = True
End Sub




Then run the GetCoName macro.


Have a great day,
Stan

[shrivallabha]

I am not very sure about this but probably defining the exact range rather than the whole column has an effect on the speed of calculation. This is plain guess. Your formula:

=IFERROR(IFERROR(IFERROR(IFERROR(INDEX(Data!A:F,MATCH(Formula!A2,Data!B:B,0 ),1),INDEX(Data!A:F,MATCH(Formula!A2,Data!C:C,0),1)),INDEX(Data!A:F,MATCH(F ormula!A2,Data!D:D,0),1)),INDEX(Data!A:F,MATCH(Formula!A2,Data!E:E,0),1)),I NDEX(Data!A:F,MATCH(Formula!A2,Data!F:F,0),1))

can be reduced bit by using this formula:

=IFERROR(INDIRECT("Data!A$"&(IFERROR(MATCH(A2,Data!B$1:B$125,0),IFERROR(MATCH(A2,Data!C$1:C$125,0),IFER ROR(MATCH(A2,Data!D$1:D$125,0),IFERROR(MATCH(A2,Data!E$1:E$125,0),IFERROR(M ATCH(A2,Data!F$1:F$125,0),0))))))),"NOT FOUND")

See if the change in formula logic helps improve speed.

[vishwakarma]

Thanks a lot guys for your valuable suggestions and solutions...

But I need one more solution from u guys,

What if I want to retrieve more than one column. Let say, In my attached sheet I had only to retrieve the Company name , what is I have more than one column for e.g., Company Address or Company City and want to retrieve them as well.


Sorry for not mentioning this earlier,


Thanks,

[stanleydgrom]

vishwakarma,

We will need another workbook that contains the additional information.

Worksheet Formula with the desired output.

And, worksheet Data with the additional information.


Have a great day,
Stan

[vishwakarma]

Hi,

Attached is the sample sheet with the additional info.

I've used the UDF provided by Mr. Paul Hossler and which I think will suits best for my requirement in the first column (i.e. Company Name). I have highlighted the rest of the columns in which I need the additional information.

I need one single function through which I can retrieve all the desired column values and which take lesser processing time as I have to use hundreds of domains and it usually take lots of time to process.


Help appreciated...


Thanks,

[Paul_Hossler]

Expanded to return 4 pieces of information


NOTE: The formula is entered as an array

So select all 4 cells( B3:E3) and use Control+Shift+Enter to enter the formula as an array. Excel will put the { } around the formula for you. You do not need to enter them

{=FindCompanyOtherData(A3,Data!A:I)}


Option Explicit
Function FindCompanyOtherData(sDomain As String, rData As Range) As Variant
Dim iRow As Long, iCol As Long

FindCompanyOtherData = Array(vbNullString, vbNullString, vbNullString, vbNullString)

With Application.WorksheetFunction
iRow = 0
For iCol = 2 To rData.Columns.Count
On Error Resume Next
iRow = .Match(sDomain, rData.Columns(iCol), 0)
On Error GoTo 0
If iRow > 0 Then
FindCompanyOtherData = rData.Cells(iRow, 1).Resize(1, 4)
Exit Function
End If
Next iCol
End With
End Function


Paul

[vishwakarma]

Thanks a lot... :yes