VLookUp or ....

[francis]

Hi

I want to lookup a value in Column D based on column A and column C, but
Vlookup failed to return the value. Is using Vlookup the right function for this
case?:dunno if not, what is the correct function to use?

Appreciate your advise in this

Thanks

[Zack Barresse]

Hello there,

Vlookup will match a criteria to the left-most column in the array which you specify and return a value on the same row of the column you specify (third syntactical piece). Can you tell us what your information is exactly and tell us an example or two? If you don't tell us much more, solutions will not be so specific and may not work.

[Bob Phillips]

Probably not, try


=INDEX(D1:D100,MATCH(1,(A1:A100="some string")*(C1:C100=number),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.
When editing the formula, it must again be array-entered.

[francis]

Hi

Thanks for responsing. I have attached a sample. I want the value in Col. D
to appear in the textbox name txtSI based on the based data in Col.A and Col.D. Which is the correct function to use :help

Thanks.

[Bob Phillips]

Private Sub txtac_AfterUpdate()
With txtAc
If txtAc.Text <> "" And txtCurr.Text <> "" Then GetMatch
End With
End Sub

Private Sub txtCurr_AfterUpdate()
With txtCurr
If txtAc.Text <> "" And txtCurr.Text <> "" Then GetMatch
End With
End Sub

Sub GetMatch()

Dim tmp
On Error Resume Next
tmp = ActiveSheet.Evaluate("INDEX(D1:D100,MATCH(1,(A1:A100=""" & txtAc.Text & _
""")*(C1:C100=""" & txtCurr.Text & """),0))")
On Error GoTo 0
If Not IsError(tmp) Then TxtSI.Text = tmp
End Sub

[francis]

Hi Xld,

Thanks, but it don't works. :banghead: Am I right that the codes go behind the userform ? No action from Excel.
Also how do I include these lines into your codes?

if txtName.Text = "" Then
MsgBox "Invalid Entry"
Else


Thanks

[Bob Phillips]

It worked fine for me, in what way does it not work.

I have no idea where that code should go, that is a design decision. You need to determine at what event that you would want that check t be triggered by, and add it to that event.

Personally, I would load it automatically after the account number is selected. Furthermore, I would make the account number a combobox, and pre-load it with the possible values, then make the currency another combobox and load that with the values for that account number.

[francis]

Hi Xld,

Agreed that comboboxes are great tools. I have close to about 1500 unique entries and the dropdown would be very long. Further, I would also like to know how to do this lookup as mentioned.

The attached file have your codes behind the Userform. The textboxes for both Account Name and SI Detail doesn't show after the value have been entered.

Thanks for the guidance.

[Zack Barresse]

It works just fine, but you have trailing spaces in some of the values (mostly the "USD" values) which is throwing off your results.

Change out your txtCurr control for a combobox named cbCurr and use the following code...

Private Sub txtac_AfterUpdate()
If txtAc.Text = "" Then
MsgBox "Fill in Account Number please."
txtAc.SetFocus: Exit Sub
End If
Call GetMatch
End Sub

Private Sub cbCurr_Change()
If cbCurr.Text = "" Then
MsgBox "Fill in Currency please."
cbCurr.SetFocus: Exit Sub
End If
Call GetMatch
End Sub

Sub GetMatch()
Dim tmp
On Error Resume Next
tmp = ActiveSheet.Evaluate("INDEX(D1:D100,MATCH(1,(A1:A100=""" & txtAc.Text & _
""")*(C1:C100=""" & cbCurr.Text & """),0))")
On Error GoTo 0
If Not IsError(tmp) Then
TxtSI.Text = tmp
Else
TxtSI.Text = ""
End If
End Sub

Private Sub UserForm_Initialize()
Dim i As Long, dic As Object, dicElm As Variant
Set dic = CreateObject("Scripting.Dictionary")
With ThisWorkbook.Worksheets("SI")
For i = 2 To .Cells(.Rows.Count, "C").End(xlUp).Row
If Not dic.exists(Trim(.Cells(i, "C").Value)) Then
dic.Add Trim(.Cells(i, "C").Value), Trim(.Cells(i, "C").Value)
End If
Next i
End With
For Each dicElm In dic
Me.cbCurr.AddItem dicElm
Next dicElm
End Sub

HTH

[Bob Phillips]

This is what I was thinking of, all combo boxes.

remember, comboboxes allow selective picking, typing letters takes you to the first item of that sort.

BTW, what are the Search and Next buttons for?

[francis]

It works just fine, but you have trailing spaces in some of the values (mostly the "USD" values) which is throwing off your results.

How do you find out the trailing spaces?



tmp = ActiveSheet.Evaluate("INDEX(D1:D100,MATCH(1,(A1:A100=""" & txtAc.Text & _
""")*(C1:C100=""" & cbCurr.Text & """),0))")

Appreciate that you would explain this formula


For i = 2 To .Cells(.Rows.Count, "C").End(xlUp).Row
If Not dic.exists(Trim(.Cells(i, "C").Value)) Then
dic.Add Trim(.Cells(i, "C").Value), Trim(.Cells(i, "C").Value)
End If
Next i
End With
For Each dicElm In dic
Me.cbCurr.AddItem dicElm
Next dicElm

I don't understand this, I am lost at this:banghead:


Thank you for your guidance and patience

[francis]

This is what I was thinking of, all combo boxes.
remember, comboboxes allow selective picking, typing letters takes you to the first item of that sort.

Thank you for the idea, something new I learn.


BTW, what are the Search and Next buttons for?

The Search button is a dummy button where it attract the user to focus so that the focus is not on the input textbox which will show the value of the lookup.

The Next button is to clear all the textboxes.

Appreciate your guidance and assistance.

[Bob Phillips]

Next doesn't seem a good caption for that button. People expect Next to get the next one, you might be getter to caption it Clear.

So, are you sorted now?

[francis]

Hi Zack,

The codes you post doesn't seem to work on the actxt. After I input the
ac. no, the ac name does not appear.

Additionally, how does this codes works


tmp = ActiveSheet.Evaluate("INDEX(D1:D100,MATCH(1,(A1:A100=""" & txtAc.Text & _
""")*(C1:C100=""" & cbCurr.Text & """),0))")
On Error Goto 0
If Not IsError(tmp) Then
TxtSI.Text = tmp
Else
TxtSI.Text = ""

[Aussiebear]

Bob & Zack, Is it possible to ..... have the entry method whereby if you were to type the( say frist three values) it filters out the 1500 long validation list to those only with combinations that start with the first three typed values?

[Charlize]

Bob & Zack, Is it possible to ..... have the entry method whereby if you were to type the( say frist three values) it filters out the 1500 long validation list to those only with combinations that start with the first three typed values?
You could use the combobox_change event to rebuild the list when the length of the combobox.value = 3

Charlize

[Bob Phillips]

Bob & Zack, Is it possible to ..... have the entry method whereby if you were to type the( say frist three values) it filters out the 1500 long validation list to those only with combinations that start with the first three typed values?

The problem that I see is the way that VBA handles the box type-in. If you were to take some action based upon that type-in, you are chnaging the data, and triggering events. Controlling all of the possibilities there could get inordinately complex.

My preferred method in such situations is to have another combobox with data categories (be it first letter, states or whatever), select from that, then build a second dependent combobox. This approach is very simple.

[Zack Barresse]

One problem is your currency values have some trailing spaces, hence the Trim() in my code...