I need to match Column A and Column B values in Sheet 1 with Column A and Column B values in Sheet 2. If both are same then Copy C values from Sheet 2, and paste in Sheet 1 in C. I will enter values manually in sheet 2 Column C.Here each country will have 2 or more Number. So, both Column A and Column B must match.
I used the formula below. But not working. Most of the Column A and B values are not in order. Help me
=INDEX(Sheet2!$C:$C; MATCH(Sheet1!$A2:B2; Sheet2!$A:$B; 0);COLUMNS($A:B))
You can use the following formula to return what you're looking for. It is an array formula so will need to be entered with Ctrl+Shift+Enter
=INDEX(Sheet2!$C$2:$C$22; MATCH(1; (Sheet2!$A$2:$A$22=Sheet1!$A2)*(Sheet2!$B$2:$B$22=Sheet1!$B2);0))
As it is an array formula I recommend defining your ranges from beginning to end instead of just selecting the whole column. Non-array formulas Excel actively finds the beginning and end of the range and only calculates that subset; however, with array formulas it considers the whole range (even if there's nothing in it) so it can suddenly take a very long time even when there isn't much being calculated
Use the following as a matrix formula:
=INDEX(Sheet2!$C:$C; MATCH(Sheet1!$A2&$B2; Sheet2!$A&$B; 0))
Paste this into cell C2:
=INDEX(C2:C22,MATCH(G2&H2,A2:A22&B2:B22,0))
and use Ctrl+Shift+Enter instead of Enter since it's an array formula.
Then copy that cell as many rows down as needed.
Here are some more examples.
