Using Index and Match to look for two criterias

[iefraim]

Hello Everyone! Hope you all are fine.

First, I want to apologize if there's already a thread about this subject, I searched a lot and didn't found anything that could help me with my problem.

So, I'm trying to use VBA userform to look for two criterias from my worksheet.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A1[/TD]
[TD="align: center"]A2[/TD]
[TD="align: center"]A3[/TD]
[/TR]
[TR]
[TD="align: center"]Product[/TD]
[TD="align: center"]Flavor[/TD]
[TD="align: center"]Price[/TD]
[/TR]
[TR]
[TD]Ice Cream[/TD]
[TD]Chocolate[/TD]
[TD]5 BRL[/TD]
[/TR]
[TR]
[TD]Ice Cream[/TD]
[TD]Strawberry[/TD]
[TD]6 BRL[/TD]
[/TR]
[TR]
[TD]Popsicle[/TD]
[TD]Grape[/TD]
[TD]2 BRL[/TD]
[/TR]
[TR]
[TD]Popsicle[/TD]
[TD]Vanilla[/TD]
[TD]2,50 BRL[/TD]
[/TR]
</tbody>[/TABLE]

So, what I'm trying to do is a userform that look for both information (Product and Flavor) typed by the customer, like this (It's in portuguese, but it won't confuse you):

But when I type to search any product/flavor that ISN'T the first item of the list, it doesn't find and says that it was unable to get the Index property...

I'm using this code:

Private Sub CommandButton1_Click()
Dim produto As String
Dim Sabor As String


produto = TextBox1.Text
Sabor = TextBox2.Text


TextBox3 = "R$" & Application.WorksheetFunction.Index(Range("C2:C5"), _
Application.WorksheetFunction.Match(produto, Range("A2:A5"), 0), _
Application.WorksheetFunction.Match(Sabor, Range("B2:B5"), 0))
End Sub

Thanks in advance.

 

[baitmaster]

you could use an Excel formula to evaluate the answer, and just pass the result to your form. With your data in cells A1:C4, and the 2 criteria in cells A6, B6, then an array formula (entered using Shift + Ctrl + Enter, not just Enter) will do this: =INDEX(C1:C4,MATCH(A6&B6,A1:A4&B1:B4,0))

 

[iefraim]

Wow man, it's a great ideia, but is there a way to make it without having to evaluate? I used this workbook as an example, but in the other one, I'll find more than one thing and put to a textbot to show, than I'll "dim" them to make my macro be faster.

I appreciate your answer and it's a very good idea, I can use it, but first I want to know if this is the only way to get the multi-criteria index-match, 'cause the userform will look for more than one information based on the things the user typed, and evaluate every single one will make the code even bigger I guess.

Thank you, man.

 

[baitmaster]

With Excel & VBA there is never only one way. You can usually use many combinations of formula and code, but your aim should be to find the most efficient and simple way to do it. For one pair of products I think a formula will be easier than code and so I suggested that. However a VBA approach would also be possible

This approach "probably" works but I haven't tested it. I've created a function that returns a text string, and which is called by your command button. I've done it this way because you can easily re-use this approach in other places

The function needs two text strings to be passed into it, and returns a new text string if both criteria are met, or "" if none found. You'll need to name your range of data, I've called it "myPrices"

Option Explicit

Private Sub CommandButton1_Click()
TextBox3 = getValue(TextBox1.Text, TextBox2.Text)
End Sub

Function getValue(str1 As String, str2 As String) As String
Dim cl As Range
For Each cl In Range("myPrices").Columns(1).Cells
    If cl = str1 Then
        If cl.Offset(0, 1) = str2 Then
            getValue = "R$ " & cl.Offset(0, 2)
            Exit Function
        End If
    End If
Next cl

End Function