Share via

Index Match Multiple Matches

Anonymous
2022-07-25T15:05:52+00:00

Hi,

In screenshot below I am trying to use an index match function based on the data in columns F and G - to retrieve the correct size code from column AF - I can't seem to get correct index match function if someone can advise?

Microsoft 365 and Office | Excel | For business | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-07-25T16:01:49+00:00

    Dear Joe,

    Good day! Thank you for posting to Microsoft Community. We are happy to help you.

    Below is an example for using INDEX & MATCH for multiple criteria. You may edit it and input in your own workbook.

    =INDEX(H1:H7,MATCH(1,(F1:F7=B2)*(G1:G7=A2),0))

    If you have further query for this, feel free to post back.

    Sincerely,

    Ryan | Microsoft Community Moderator

    ***Note: In the event that you're unable to reply to this thread, please ensure that your Email address is verified in the Community Website by clicking on Your Account Name > "My Profile" > "Edit Profile" > Add your Email Address > tick "Receive email notifications" checkbox > click on "Save".***

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2022-07-25T15:54:28+00:00

    =FILTER(AF:AF,(AD:AD=G2)*(AE:AE=F2))

    With INDEX, you could use this, array=entered with Ctrl-Shift-Enter (in desktop Excel)

    =INDEX(AF:AF,SUM((AD2:AD7=G2)*(AE2:AE7=F2)*ROW(AE2:AE7)))

    2 people found this answer helpful.
    0 comments