Index Match with multiple criteria and partial match

Desauv · Jun 11th 2019

Hi

So I'm trying to make a lookup formula which have multiple criteria and also partial match.

This is the formula that I've been trying so far, to find the value 50. However it doesn't work.

=INDEX(I10:I13;MATCH(1;("*"&I4&"*"=H10:H13)*("*"&I5&"*"=H10:H13);0))

[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Excel Example.png","data-attachmentid":1219158}[/ATTACH]

I've tried this formula also, but here I don't know how to insert multiple criteria

=INDEX(I10:I13;MATCH("*"&I5&"*";H10:H13;0))

I hope someone can help

Stefan

**alansidman** · Jun 11th 2019

Could not think of a formula solution, but here is a vba solution for you

Code

Option Explicit
Option Compare Text






Sub Desauv()
    Dim crit1 As String
    Dim crit2 As String
    crit1 = Range("I4").Value2
    crit2 = Range("I5").Value2
    Dim i As Long, lr As Long
    lr = Range("I" & Rows.Count).End(xlUp).Row
    For i = 10 To lr
        If InStr(Range("H" & i), crit1) > 0 Then
            If InStr(Range("H" & i), crit2) > 0 Then
                MsgBox ("The Answer is " & Range("I" & i).Value2)
            End If
        End If
    Next i
End Sub

Display More

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

Niraj · Jun 11th 2019

Copy this "=INDEX(I10:I13,MATCH("*"&I4&"*"&"*"&I5&"*",H10:H13&H10:H13,0))" and press Ctrl+Shift+Enter

Index Match with multiple criteria and partial match

Participate now!

Share