Index Match with multiple criteria and partial match

  • 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

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




    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