Xlookup or Index/Match with two criteria

• Formula: like xllookup or Index/Match that uses two criteria.

If Sheet1 A2 is found in range Sheet2 A:A then return the cell in the range of Sheet2 \$B:\$IE that matches Sheet1 B\$1

Test 2 criteria.xlsx

• 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,

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,

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