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
Xlookup or Index/Match with two criteria
-
Hello,
In cell B2, you can test following
Code=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
-
Display More
Hello,
In cell B2, you can test following
Code=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
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=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
-
-
Once you have tested the formula ... feel free to share your comments
-
Display More
Hello,
You can modify your formula
Code=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
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
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!