Use Lookup formula with the MID function

Banjara

New Member
Joined
Dec 24, 2012
Messages
14
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
 
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
 
Upvote 0
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)),"")
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top