Use Lookup formula with the MID function

[Banjara]

Hi All,

Reference to this thread http://www.mrexcel.com/forum/excel-questions/423808-use-lookup-formula-mid-function.html

I want to use same formula in bit different way. But I can't figure it out how?

I have a Date "26 January 2013"

Sheet 1
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name "ABC"[/TD]
[TD]Contract start date[/TD]
[TD]Contract End Date[/TD]
[TD]Joining Date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ABC[/TD]
[TD]26 January 2013[/TD]
[TD]26 January 2016[/TD]
[TD]28 January 2013[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Lookup(Sheet2!B1, in Sheet 1 Range, pick value if mid(Sheet1!B1,3,4)=Sheet2!B1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet2!B1 will be month (Jan) and Sheet2!C1 (2016) will be year put in manually, According to input it may lookup and return the Names.

Is it possible anyway ?

Looking forward for your kind guidance. Thanks in Advance

 

[Aladin Akyurek]

What is the input and what is the output you want to see?

 

[Banjara]

Thanks Aladin for Prompt response

Input is 26 January 2013 a date mentioned in sheet 1

I want to give month and year separately or together , it should lookup date in Sheet1 and give the employee name

 

[Aladin Akyurek]

Thanks Aladin for Prompt response

Input is 26 January 2013 a date mentioned in sheet 1

I want to give month and year separately or together , it should lookup date in Sheet1 and give the employee name

The data is on Sheet1, A1:D20, with headers in A1:D1.

Sheet2

B1: Jan
C1: 2016

D1, control+shift+enter, not just enter:

=IFERROR(INDEX(Sheet1!$A$2:$A$20,MATCH(1,IF(TEXT(Sheet1!$C$2:$C$20,"mmm")=$B1,IF(YEAR(Sheet1!$C$2:$C$20)=$C1,1)),0)),"")