Xlookup or Index/Match with two criteria

  • Hello,


    In cell B2, you can test following


    Code
    1. =INDEX(Sheet2!$A$1:$M$37,MATCH(Sheet1!$A2,Sheet2!$A:$A,0),MATCH(Sheet1!B$1,Sheet2!$1:$1,0))


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Works great but I am having a problem expanding it to the right and down. I have 886 rows down and a long way to the right.

  • Hello,


    You can modify your formula


    Code
    1. =INDEX(Sheet2!$A$1:$IM$886,MATCH(Sheet1!$A2,Sheet2!$A:$A,0),MATCH(Sheet1!B$1,Sheet2!$1:$1,0))


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Once you have tested the formula ... feel free to share your comments

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Could you help me understand the formula a little better so I can expand it down to 886 rows and also many columns to the right?

  • Could you help me understand the formula a little better so I can expand it down to 886 rows and also many columns to the right?

    Hello,


    Two comments :


    1. Did you see the message # 5 ... formula is already adjusted ...


    2. To understand the mechanics of the combination Index / Match for a two-way lookup :


    https://www.excel-easy.com/examples/two-way-lookup.html


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)