How to get the value in multiple criteria of two columns ?

[Jyotirmaya]

I have data in Sheet1 and in Sheet2.
I have three columns in Sheet1 Bank, Branch,Branch Code in A B & C column.
In sheet2 I have Bank Branch Code in A B & C column
In C2 of Sheet1 I want that IF A2 and B2 matches the criteria it will get the Branch code from sheet2 Range A2:C100.


For example IF Sheet1 A2 is STATE BANK B2 is NEW DELHI
Sheet2 A2 is STATE BANK B2 is NEW DELHI C3 is STATE0005
Then I want the C2 of Sheet1 as STATE0005

I cant use VLOOKUP here because it uses two columns, may be index and match may work. what should be the formula ?

 

[Yongle]

With helper column
formula in sheet1 D2 copied down
=A2&B2

And then in sheet2 , the formula in C2 copied down is
=INDEX(Sheet1!C:C,MATCH(A2&B2,Sheet1!D:D,0))


Without helper column
in sheet2 ARRAY formula in C2 copied down
=INDEX(Sheet1!C:C,MATCH(A2&B2,Sheet1!A:A&Sheet1!B:B,0))

An array formula is committed with {CTRL}{SHIFT}{ENTER} and will then look like this
{=INDEX(Sheet1!C:C,MATCH(A2&B2,Sheet1!A:A&Sheet1!B:B,0))}
- does not work if you type in the { }


To target a specific range - all ranges must be same size (also an ARRAY formula)
=INDEX(Sheet1!C$2:C$5000,MATCH(A2&B2,Sheet1!A$2:A$5000&Sheet1!B$2:B$5000,0))

 

[Jyotirmaya]

With helper column
formula in sheet1 D2 copied down
=A2&B2

And then in sheet2 , the formula in C2 copied down is
=INDEX(Sheet1!C:C,MATCH(A2&B2,Sheet1!D:D,0))


Without helper column
in sheet2 ARRAY formula in C2 copied down
=INDEX(Sheet1!C:C,MATCH(A2&B2,Sheet1!A:A&Sheet1!B:B,0))

An array formula is committed with {CTRL}{SHIFT}{ENTER} and will then look like this
{=INDEX(Sheet1!C:C,MATCH(A2&B2,Sheet1!A:A&Sheet1!B:B,0))}
- does not work if you type in the { }


To target a specific range - all ranges must be same size (also an ARRAY formula)
=INDEX(Sheet1!C$2:C$5000,MATCH(A2&B2,Sheet1!A$2:A$5000&Sheet1!B$2:B$5000,0))

thank you