Thread: vb vlookup using 2 columns

Hi

I am trying to do a vlookup using 2 columns i.e first name , last name to return a day

I have the vb code for looking up the first and second columns, but dont know how to use them together to return the third column.

An example is below:

a b c
1 adam smith Monday
2 adam jones Tuesday
3 steve smith Wednesday
4 steve jones Thursday

Id like to lookup columns A and B to return C. I wont post my code incase I need something completely different!

Thanks in advance

Thanks for the reply

I have got it to work in excel, but I have tried to adapt it to my vb userform

using the same example data - on sheet "Rota"

a b c
1 adam smith Monday
2 adam jones Tuesday
3 steve smith Wednesday
4 steve jones Thursday

Txt_Day.Value = Application.WorksheetFunction.FormulaArray (INDEX(Sheets("Rota").Range("C:C"),Application.WorksheetFunction.MATCH(Cmb_First_Name.text)&(Cmb_Last_Name.text),Sheets("Rota").Range ("A:A") & Sheets("Rota").Range("B:B"),0))

I get an error sayng the argument "match" is not optional.

Thanks

I just cannot get the formulas to work in VBA - here is a set of non array formulas that will do the same
=INDEX(C1:C4,MATCH(1,INDEX((A1:A4="adam")*(B1:B4="jones"),0,1),0))
=INDEX(C1:C5,SUMPRODUCT((A1:A5="adam")*(B1:B5="jones")*ROW(B1:B5)))
One option would be to write the value to the spreadsheet using the following then read it back into the textbox

Range("A8").FormulaArray = "=INDEX($D$1:$D$4,MATCH(B1&C1,$B$1:$B$4&$C$1:$C$4,0))"
TxtDay.Value = range("A8").Value

or use a lot more code using FIND to get the answer

Sub test()
Dim fCell As Range, lCell As Range, fAdd As String
With Range("A1").EntireColumn
    Set lCell = .Cells(.Cells.Count)
End With
Set fCell = Range("A1").EntireColumn.Find(what:=Cmb_First_Name.Text, after:=lCell)
If Not fCell Is Nothing Then
    fAdd = fCell.Address
End If
Do Until fCell Is Nothing
    If fCell.Offset(0, 1) = cmb_last_name.Text Then
    x = fCell.Offset(0, 2)
    Exit Do
    Else
    Set fCell = Range("A1").EntireColumn.FindNext(after:=fCell)
    End If
    If fCell.Address = fAdd Then
        Exit Do
    End If
Loop
If x <> "" Then
Txt_Day.Value = x
End If
End Sub

Hope this helps.
Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”

Hi Guys,

Try this. I have used Column G as demostration. Maybe you can adjust to suit. If any day is left out, the word "Empty" will show in that specific row:

Copy this Code to a Module, and test it.

Sub Test()
Dim LR As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Range("G1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
("=IF(RC[-6]="""","""",IF(INDEX(R1C3:R4C3,MATCH(1,INDEX((R1C1:R4C1=RC[-6])*(R1C2:R4C2=RC[-5]),0,1),0))=0,""Empty"",INDEX(R1C3:R4C3,MATCH(1,INDEX((R1C1:R4C1=RC[-6])*(R1C2:R4C2=RC[-5]),0,1),0))))")
Range("G1").AutoFill Destination:=Range("G1:G" & LR)
End Sub

Hope it helps!

This was doing my head in - the following should work

Sub test()
Dim a As Variant
Dim Cmb_First_Name As String, Cmb_Last_Name As String
Cmb_Last_Name = "Jones": Cmb_First_Name = "Adam"
a = Evaluate("=INDEX(C:C,MATCH(1,INDEX((A:A=" & """" & Cmb_First_Name & """" & ")*(B:B=" & """" & Cmb_Last_Name & """" & "),0,1),0))")
End Sub

Since I did not set up comboboxes you should just have to try

dim a as variant
a = Evaluate("=INDEX(C:C,MATCH(1,INDEX((A:A=" & """" & Cmb_First_Name.value & """" & ")*(B:B=" & """" & Cmb_Last_Name.value & """" & "),0,1),0))")
Txt_Day.value = a
'or just use
Txt_Day.Value = Evaluate("=INDEX(C:C,MATCH(1,INDEX((A:A=" & """" & Cmb_First_Name.Value & """" & ")*(B:B=" & """" & Cmb_Last_Name.Value & """" & "),0,1),0))")

I assume Cmb_First_Name and Cmb_Last_Name are comboboxes?

Let me know how you get on

Hello cooket4,

May I suggest you upload a sample Workbook of what you have, and an explanation as to what exactly you want. Doing so will help us to focus on the actual issue, without assuming any possible solutions which may be way off track.

@smuzoen,

Re:

Cmb_Last_Name = "Jones": Cmb_First_Name = "Adam"

How would that help if the names are "Zabor" and "Charlie" ?

Just wondering...

@ Winon
That is what error handling and code is for - he asked how to incorporate the Comboboxes (I assume) into a formula with worksheet functions which I supplied. He said he was using a userform so obviously the variables are controls

If IsError(a) Then
MsgBox "Not found"
End If

I had already posted an answer along the lines of your suggestion

Range("A8").FormulaArray = "=INDEX($D$1:$D$4,MATCH(B1&C1,$B$1:$B$4&$C$1:$C$4,0))"
TxtDay.Value = range("A8").Value

The point was how to incorporate userform controls into the formula.

@ smuzoen,


Re:

If IsError(a) Then
MsgBox "Not found"
End If

Thanks for responding. I do know about ErrorHandling, and this pretty much completes your Code, without leaving cooket4 wondering what is happening if nothing was found.

I also believe that you may be very close with what you are assuming cooket4 wants.

Hope you get a good Rep for your efforts!

@ cooket4,

It would be appreciated if you could confirm if your problem was resolved.