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

	A	B	C	D
1	Name "ABC"	Contract start date	Contract End Date	Joining Date
2	ABC	26 January 2013	26 January 2016	28 January 2013

<tbody>
</tbody>

Sheet 2

	A	B	C	D
1		Jan	2016
2		Lookup(Sheet2!B1, in Sheet 1 Range, pick value if mid(Sheet1!B1,3,4)=Sheet2!B1

<tbody>
</tbody>

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)),"")