0

I have an excel sheet table with the following data:

enter image description here

In VBA how to search and match for both values in columns A and B and return row value in column C. Example: I need to search for the exact match of c+c1 and have as result yy

Many thanks for the help

Vityata
  • 42,633
  • 8
  • 55
  • 100
pizzetta72
  • 1
  • 1
  • Result as yy means what? There are no row numbers showing either in sample. What have you tried? Did you mean to return x5? https://exceljet.net/formula/index-and-match-with-multiple-criteria – QHarr Jul 27 '18 at 10:31
  • Possible duplicate of [Lookup using INDEX and MATCH with two criteria](https://stackoverflow.com/questions/18767439/lookup-using-index-and-match-with-two-criteria) – QHarr Jul 27 '18 at 10:33
  • @QHarr you're right. I made a mistake. The final result should be X5 – pizzetta72 Jul 27 '18 at 11:56

2 Answers2

0

Use If statements when you loop through the rows like so:

If ws.cells(i,1).Value = c And ws.cells(i,2).value = c1 Then

result = yy
End If

Hope it helps!

jcrizk
  • 605
  • 5
  • 15
0

U can create a 4th column, this column will be your key column to use in VBA. In this column you will concatenate the A and B values, after that we create a code that search the concat and return the 4th cell on the right.

table .

Sub Example()

Dim keyRange As Range
Set keyRange = Planilha1.Range("A2:A8")

Dim SearchValue1, SearchValue2 As String

SearchValue1 = "a"
SearchValue2 = "a2"

Dim lin As Integer
lin = Application.WorksheetFunction.Match(SearchValue1 & SearchValue2, keyRange, 0)

Dim answer As String
answer = Planilha1.Range("A2:D8").Cells(lin, 4)

Debug.Print answer


End Sub
skulden
  • 380
  • 1
  • 10