How to use INDEX and MATCH in VBA + Excel with multiple criterias?

Question

I have a sheet with some tabs, which in the first one there is the following table:

Tab1 - Table 1

In the second tab I type the "cliente" and "tipo" fields, and I'd like to get the ID as return.

I'm using the following code:

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

I don't know exactly what's wrong, but I've tried with a lots of ways, including some examples spliting the code and using Evaluate function as well. But, nothing happens.

How could I do it?

have a look at the world grand master of Excel INDEX/MATCH - you might find what you're looking for — Our Man in Bananas
– Our Man in Bananas, Commented Jul 19, 2018 at 15:08
You might also want to consider using a PivotTable and Slicers...might be easier for users to click on Slicers and have the PivotTable filtered accordingly. — jeffreyweir
– jeffreyweir, Commented Jul 19, 2018 at 15:32

FocusWiz · Accepted Answer · 2018-07-19 15:46:36Z

4

I prefer to use regular formulas for something like this.

Assuming that Columns A, B, and C are laid out as per your example and that the lookup value for Cliente is in F1 and Tipo is in F2, this would be a formula not entered as an array:

=INDEX(A1:A6,INDEX(MATCH(1,(F1=B1:B6)*(F2=C1:C6),0),1))

This would be the equivalent array formula (entered with [Ctrl]+[Shift]+[Enter]):

{=INDEX(A1:A6,MATCH(1,(F1=B1:B6)*(F2=C1:C6),0))}

See below....

answered Jul 19, 2018 at 15:46

FocusWiz

5431 gold badge3 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vityata Over a year ago

The two indexes, avoiding the array formula are impressive.

FocusWiz Over a year ago

I find that I am my own worst enemy when it comes to array formulas. I have fat-fingered them so many times that I use a special color for them in some spreadsheets.

Vityata Over a year ago

Noone likes array formulas, they cause too many problems. I would rather convince someone to use VBA than write one.

Vityata · Accepted Answer · 2018-07-19 15:52:36Z

1

In general, =INDEX(MATCH, MATCH) is not an array formula, but a normal one. However, your case is different - you are not matching rows and columns, but two columns, thus it should be. Array formulas are implented with Ctrl+Shift+Enter.

If you have your data like this:

Then this is the Array Formula in G1:

=INDEX(A1:A6,MATCH(1,(E1=B1:B6)*(F1=C1:C6),0))

And this is how to do it with vba:

Public Sub TestMe()
    Range("G1").FormulaArray = "=INDEX(A1:A6,MATCH(1,(E1=B1:B6)*(F1=C1:C6),0))"
End Sub

To see how this formula is working, see the calculation of Excel in the Formula>Evaluation tab (VERGLEICH is MATCH in German):

edited Jul 19, 2018 at 15:52

answered Jul 19, 2018 at 15:14

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

3 Comments

Zac Over a year ago

That's returning the incorrect ID though isn't it Vityata? As per OP's requirement, in your example case, it should return 1.. shouldn't it?

Vityata Over a year ago

@Zac - thank you for the comment, I have changed the answer to a correct one.

Zac Over a year ago

I knew I had some use :)

QHarr · Accepted Answer · 2018-07-19 15:54:16Z

1

You can use max if in an array formula

=MAX(IF(Table1[[#All],[CLIENTE]]=A2,IF(Table1[[#All],[TIPO]]=B2,Table1[[#All],[ID]])))

Enter with Ctrl+ Shift+ Enter

answered Jul 19, 2018 at 15:54

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

1 Comment

QHarr Over a year ago

Did you try the above at all?

W. Chang · Accepted Answer · 2018-07-19 15:35:01Z

0

In the other tab, go to cell A1 and type in

='Tab 1'!B1&":"&'Tab 1'!C1

copy and paste it to the whole A column.

In cell B1 enter CD or other Cliente value. In cell B2 enter D or other TIPO value. In B3 enter

=IFERROR(INDEX('Tab 1'!A:A,MATCH(B1&":"&B2,A:A,0)), "Not found!")

You should get the result in B3.

answered Jul 19, 2018 at 15:35

W. Chang

5023 silver badges12 bronze badges

Comments

Kubie · Accepted Answer · 2018-07-19 17:19:05Z

If you don't mind a programmatic solution, you can just iterate over both tabs with a VBA program rather than a formula.

In the VBA Editor, place this code in the area of your first sheet, for example "Sheet 1" or whatever you have. Don't place it in ThisWorkbook area.

Private Sub Worksheet_Change() 

Dim client As String
Dim type As String
Dim client2 As String
Dim type2 As String
Dim row As Integer
Dim row2 As Integer
Dim lastRow As Integer
Dim lastRowSheet2 As Integer

' Assuming your sheet is setup similar to Vityata's answer
lastRow = Sheets(1).Cells(Sheets(1).Rows.Count, "B").End(xlUp).row
lastRowSheet2 = Sheets(2).Cells(Sheets(2).Rows.Count, "B").End(xlUp).row

For row = 1 to lastRow
    client = Worksheets(1).Range("B" & row).Value
    type = Worksheets(1).Range("C" & row).Value

    For row2 = 1 to lastRowSheet2
        client2 = Worksheets(2).Range("B" & row2).Value
        type2 = Worksheets(2).Range("C" & row2).Value

        If client = client2 And type = type2 Then
            Worksheets(1).Range("A" & row).Value = Worksheets(2).Range("A" & row2).Value 
        End If
    Next
Next

End Sub

Of course, you will need to change the 'A's, 'B's, and 'C's to fit your workbook and desired ranges. Good luck.

Diego de Lima · Accepted Answer · 2018-07-23 12:34:24Z

0

Thank you all for supporting me. I started trying by the Vityata suggestion and it worked.

My final code is: ActiveCell.Offset(0, -2).FormulaArray = "=INDEX(Table1[ID],MATCH(1,(" & cliente & "=Table1[CLIENTE])*(" & valor & "=Table1[TIPO]),0))"

answered Jul 23, 2018 at 12:34

Diego de Lima

4713 gold badges8 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to use INDEX and MATCH in VBA + Excel with multiple criterias?

6 Answers 6

3 Comments

3 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

3 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related