vlookup and match 1 more column & then pull value of another column

[hsandeep]

Sheet1 is input & Sheet2 is output.
Sheet1:
A2-A9999=alphanumeric values
B2-B9999=alphanumeric values (example: ENGLAND, USA, GERMANY, etc)
Sheet2:
C2-C9999=alphanumeric values
D2-D9999=alphanumeric values (example: ENGLAND)
E2-E9999=i want respective M2-M9999 column of Sheet1 using vlookup on Sheet1 (Table_Array=A2-M9999), Lookup_value=Sheet2 C2-C9999 provided D2-D9999 also matches exactly with B2-B9999.

Thanks for help.

 

[Aladin Akyurek]

Sheet1 is input & Sheet2 is output.
Sheet1:
A2-A9999=alphanumeric values
B2-B9999=alphanumeric values (example: ENGLAND, USA, GERMANY, etc)
Sheet2:
C2-C9999=alphanumeric values
D2-D9999=alphanumeric values (example: ENGLAND)
E2-E9999=i want respective M2-M9999 column of Sheet1 using vlookup on Sheet1 (Table_Array=A2-M9999), Lookup_value=Sheet2 C2-C9999 provided D2-D9999 also matches exactly with B2-B9999.

Thanks for help.

I guess you mean...

E2, Sheet2

=INDEX(Sheet1!$M$2:$M$9999,
  MATCH(1,IF(Sheet1!$A$2:$A$9999=C2,IF(Sheet1!$B$2:$B$9999=D2,1)),0))

which must be confirmed with control+shift+enter, not just enter.

 

[hsandeep]

As always,
WORKS. Thanks.
Can it be without control+shift+enter

 

[Marcol]

Maybe non-CSE

=INDEX(Sheet1!$M$2:$M$9999,MATCH(1,INDEX((Sheet1!$A$2:$A$9999=C2)*(Sheet1!$B$2:$B$9999=D2),,1),0),1)

 

[Aladin Akyurek]

As always,
WORKS. Thanks.

You are welcome.

Can it be without control+shift+enter

By introducing another IINDEX which captures the result of conditional terms, you can avoid confirmation with CSE:

[SIZE=2][FONT=lucida console]=INDEX(Sheet1!$M$2:$M$9999,
MATCH(1,INDEX((Sheet1!$A$2:$A$9999=C2)*(Sheet1!$B$2:$B$9999=D2),0),0))[/[/FONT][/SIZE]code]

a technique I should never have introduced around here for it thwarts learning array-processing formulas and it's probably a bit more expensive that the formula it replaces. By the way, the formula is still an array type of formula.

 

[T. Valko]

Sheet1 is input & Sheet2 is output.
Sheet1:
A2-A9999=alphanumeric values
B2-B9999=alphanumeric values (example: ENGLAND, USA, GERMANY, etc)
Sheet2:
C2-C9999=alphanumeric values
D2-D9999=alphanumeric values (example: ENGLAND)
E2-E9999=i want respective M2-M9999 column of Sheet1 using vlookup on Sheet1 (Table_Array=A2-M9999), Lookup_value=Sheet2 C2-C9999 provided D2-D9999 also matches exactly with B2-B9999.

Thanks for help.

Here's another one...

Array entered**:

=INDEX(Sheet1!M2:M9999,MATCH(D2,IF(Sheet1!A2:A9999=C2,Sheet1!B2:B9999),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.