VBA Excel - Index Match with two conditions

1

I have a spreadsheet with multiple tabs. On the first tab I have the following table:

Intab2,typetheclientandtype,andIwanttoreturntheID.

ForthisIamusingthefollowingcodeinVBA:

tipo=ActiveCell.Offset(0,-1).Valuecliente=Range("D5").Value
ActiveCell.Offset(0, -2) = WorksheetFunction.Index(Sheets("Aba 1").[Tabela1[ID]], WorksheetFunction.Match(cliente & tipo, Sheets("Aba 1").[Tabela1[CLIENTE]] & Sheets("Aba 1").[Tabela1[TIPO]], 0))

I can not get the value back. I tried in many ways, even using EVALUATE or dividing the function in parts but I could not get the expected result.

How do I do it?

    
asked by anonymous 18.07.2018 / 20:50

1 answer

1

I was able to solve with the following code:

ActiveCell.Offset(0, -2).FormulaArray = "=INDEX(Tabela1[ID],MATCH(1,(cliente=Tabela1[CLIENTE])*(" & tipo & "=Tabela1[TIPO]),0))"

Tip taken from this link

    
23.07.2018 / 15:11