VBA UPDATE FORMULA EXACT WITH INPUT TYPE AND POP UP SEARCH BASED CRITERIA

[roykana]

Dear All Master,

1. I want the exact formula in the vba code in column B in the sheet "INPUT BOJ" & "INPUT M18" because using an array formula makes it slow because the data to be input is about ten thousand rows.
You can see an example of an array formula that I created in column B. So I want the vba code for the concept or process to be the same as using a formula such as, for example, type enter,
type tab then automatically, copy in column itc then automatically appears in column itm.
2. I want a pop up search based on ITM and help criteria. In the pop up, I just need to enter the selected one and then automatically enter the INPUT-m18 & INPUT-boj sheet in the ITC column.
The row info or original data record in ifg-m18 & ifg-boj is about fifty thousand rows.
I also coded the vba but this is not perfect or maybe there is another solution.

File link : VBA UPDATE FORMULA EXACT WITH INPUT TYPE AND POP UP SEARCH BASED CRITERIA.xlsm
file

Thanks
Roykana

Option Explicit
Sub multivlookupV1()
Application.ScreenUpdating = False
 With Range(Cells(2, 7), Cells(2, 7).End(xlDown))
       .FormulaR1C1 = "=IF([@ITC]="""","""",IF([@KET]=""M18"",LOOKUP(2,1/EXACT([@ITC],Table_Query_from_TT1_NOW[ITC]),Table_Query_from_TT1_NOW[ITM]),LOOKUP(2,1/EXACT([@ITC],Table_Query_from_now[ITC]),Table_Query_from_now[ITM])))"
       .Value = .Value
 End With
 Application.ScreenUpdating = True
 End Sub
Sub multivlookupV2()
Application.ScreenUpdating = False
  With Range(Cells(2, 7), Cells(2, 7).End(xlDown))
       .FormulaR1C1 = "=IF([@ITC]="""","""",LOOKUP(2,1/EXACT([@ITC],Table_Query_from_TT1_NOW[ITC]),Table_Query_from_TT1_NOW[ITM]))"
       .Value = .Value
 End With
 Application.ScreenUpdating = True
 End Sub

 

[roykana]

What means More Custom Records?
And Again I Don't Understand What you want Exactly?

what I mean is I adjusted according to the original data which had about fifty thousand record rows. I tried the vba code from you it runs very slowly and I have also attached the file. which I post there are the first 2 points you have not completed perfectly. I think I have explained enough in my post

 

[roykana]

you understand what I say for the first point you have not finished perfectly. maybe you've read my post perfectly right

 

[maabadi]

Then you want InputBOx Or Combobox to Select Criteria From Column ITC.
Is it right?

 

[roykana]

Then you want InputBOx Or Combobox to Select Criteria From Column ITC.
Is it right?

Yes it right select criteria from column ITM. It is point 2 . how for point 1, then?

 

[roykana]

Yes it right select criteria from column ITM. It is point 2 . how for point 1, then?

Use listbox too for point 2

 

[maabadi]

This is For Point 1, I worked on Point 2

Sub multivlookupV1()
Dim Lr1 As Long, Lr3 As Long, Lr4 As Long, Sh1 As Worksheet, Sh3 As Worksheet, Sh4 As Worksheet
Dim t As Double, i As Long, j As Long, E As Long, k As Long, RunT As Double
t = Timer
Set Sh1 = Sheets("IFG M18")
Set Sh3 = Sheets("INPUT BOJ")
Set Sh4 = Sheets("IFG BOJ")
Lr1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Lr3 = Sh3.Range("A" & Rows.Count).End(xlUp).Row
Lr4 = Sh4.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
 With Sh3
   .Range("G2:G" & Lr3).ClearContents
   For i = 2 To Lr3
   If .Range("A" & i).Value = "" Then
      .Range("G" & i).Value = ""
      .Range("B" & i).Value = ""
   Else
    If .Range("E" & i).Value = "M18" Then
       For j = Lr1 To 2 Step -1
       If StrComp(.Range("A" & i).Value, Sh1.Range("D" & j).Value) = 0 Then
        If .Range("G" & i).Value = "" Then
       .Range("G" & i).Value = Sh1.Range("C" & j).Value
       End If
       .Range("B" & i).Value = Sh1.Range("C" & j).Value
       End If
       Next j
     Else
       For k = Lr4 To 2 Step -1
       If StrComp(.Range("A" & i).Value, Sh4.Range("D" & k).Value) = 0 Then
       If .Range("G" & i).Value = "" Then
       .Range("G" & i).Value = Sh4.Range("C" & k).Value
       End If
       .Range("B" & i).Value = Sh4.Range("C" & k).Value
       End If
       Next k
    End If
    End If
Resum2:
  Next i
       '.Range("G2").FormulaArray = "=IFNA(IF(A2="""","""",IF(E2=""M18"",LOOKUP(2,1/EXACT(A2,'IFG M18'!$D$2:$D$" & Lr1 & "),'IFG M18'!$C$2:$C$" & Lr1 & "),LOOKUP(2,1/EXACT(A2,'IFG BOJ'!$D$2:$D$" & Lr4 & "),'IFG BOJ'!$C$2:$C$" & Lr4 & "))),"""")"
       '.Range("G2").AutoFill Destination:=.Range("G2:G" & Lr3)
       '.Range("G2:G" & Lr3).Value = .Range("G2:G" & Lr3).Value
 End With
 Application.ScreenUpdating = True
 RunT = Round(Timer - t, 3)

'Notify user in seconds
  MsgBox "This code ran successfully in " & RunT & " seconds", vbInformation
 End Sub
Sub multivlookupV2()
Dim Lr1 As Long, Lr2 As Long, Lr4 As Long, Sh1 As Worksheet, Sh2 As Worksheet, Sh4 As Worksheet
Dim t As Double, i As Long, j As Long, E As Long, k As Long, RunT As Double
t = Timer
Set Sh1 = Sheets("IFG M18")
Set Sh2 = Sheets("INPUT M18")
Set Sh4 = Sheets("IFG BOJ")
Lr1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
Lr4 = Sh4.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
 With Sh2
   .Range("G2:G" & Lr2).ClearContents
   .Range("B2:B" & Lr2).ClearContents
   For i = 2 To Lr2
   If .Range("A" & i).Value = "" Then
      .Range("G" & i).Value = ""
      .Range("B" & i).Value = ""
   Else
      For j = Lr1 To 2 Step -1
       If StrComp(.Range("A" & i).Value, Sh1.Range("D" & j).Value) = 0 Then
        If .Range("G" & i).Value = "" Then
       .Range("G" & i).Value = Sh1.Range("C" & j).Value
       End If
       .Range("B" & i).Value = Sh1.Range("C" & j).Value
       End If
       Next j
      End If
    
Resum2:
  Next i
 End With
 Application.ScreenUpdating = True
 RunT = Round(Timer - t, 3)

'Notify user in seconds
  MsgBox "This code ran successfully in " & RunT & " seconds", vbInformation
 End Sub

 

[maabadi]

AND one other thing ,
Are for you words with uppercase, lowercase or Proper case is different?

 

[roykana]

AND one other thing ,
Are for you words with uppercase, lowercase or Proper case is different?

I do not understand what you mean

 

[maabadi]

You Use Exact For Match & lookup function. Exact Used for Differntiate Between UpperCase, LowerCase And Proper Case Words
Example: With Exact : Roykana <> roykana <> ROYKANA Are different.
And with your formula Result is False
1. Is it Correct?
2. And for ListBox, Selection Case where should be Input & also Results?

 

[roykana]

You Use Exact For Match & lookup function. Exact Used for Differntiate Between UpperCase, LowerCase And Proper Case Words
Example: With Exact : Roykana <> roykana <> ROYKANA Are different.
And with your formula Result is False
1. Is it Correct?
2. And for ListBox, Selection Case where should be Input & also Results?

Example: With Exact : Roykana <> roykana <> ROYKANA Are different.
1. according to the example you give it is true
2. refers to the listbox then the criteria based on the ITM column in IFG-BOJ & IFG-M18. After I type in criteria, the list will automatically appear in the listbox after I select enter, it will automatically be filled in the itc column in input-m18 & input-boj

with your formula Result is False >>

you mean the formula I made is wrong