VBA Excel - Index Match with two conditions

Asked

Viewed 879 times

1

I have a spreadsheet with several tabs. In the first tab I have the following table: Aba 1

In tab 2 type the client and the type, and I wish to return the ID.

For this I am using the following code in VBA:

tipo = ActiveCell.Offset(0, -1).Value
cliente = 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’m not getting the value back. I tried several ways, even using EVALUATE or dividing the function by parts but could not get the expected result.

How should I do?

query quest excel-vba índices criteria

1 answer

1


I managed to solve with the following code:

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

Hint taken from this link

Browser other questions tagged query quest excel-vba índices criteria

You are not signed in. Login or sign up in order to post.