Vlookup formula to take systems month and date and search in sheet1 for same month and year and retrive adjacent cell value

[hasansha]

Hi
I have a excel sheet1 and Sheet2 in sheet 1 i have month, interest, principal and outstanding columns. i want to right a formula in sheet2 where it will take the todays month and year and search for sheet1 column month and return the outstanding column value. Please help me out with the formula. Below is my sheet and i have tried this formula

=VLOOKUP(TEXT(TODAY(),"mmmm")&"-"&YEAR(TODAY()),Sheet1!A1:F49,6,TRUE)

S.No Month EMI Interest Principle Loan Outstanding
1 April-2016 10589 5737 4852 345148
2 May-2016 10589 5658 4932 340216
3 June-2016 10589 5577 5012 335204
4 July-2016 10589 5495 5095 330109
5 August-2016 10589 5411 5178 324931
6 September-2016 10589 5326 5263 319668
7 October-2016 10589 5240 5349 314319
8 November-2016 10589 5152 5437 308882
9 December-2016 10589 5063 5526 303356

 

[steve the fish]

Hi. Try one of these:

=VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY()),1),Sheet1!B:F,5,0)
=VLOOKUP(TEXT(TODAY(),"mmmm-yyyy"),Sheet1!B:F,5,0)

 

[hasansha]

both the functions not working its showing #NA

Hi. Try one of these:

=VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY()),1),Sheet1!B:F,5,0)
=VLOOKUP(TEXT(TODAY(),"mmmm-yyyy"),Sheet1!B:F,5,0)

 

[steve the fish]

Is March-2018 in your column B?

 

[hasansha]

Yes


QUOTE=steve the fish;5020952]Is March-2018 in your column B?[/QUOTE]

 

[hasansha]

<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> <col style="width:48pt" width="64" span="3"> <col style="mso-width-source:userset;mso-width-alt:4425;width:91pt" width="121"> </colgroup><tbody>
[TD="class: xl63, width: 64"]Below is sheet



A
S.No
[/TD]
[TD="class: xl69, width: 103"]B
Month
[/TD]
[TD="class: xl64, width: 64"]C
EMI
[/TD]
[TD="class: xl64, width: 64"]D
Interest
[/TD]
[TD="class: xl64, width: 64"]E
Principle
[/TD]
[TD="class: xl64, width: 121"]F
Loan Outstanding
[/TD]

[TD="class: xl65"]1[/TD]
[TD="class: xl70"]April-2016[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]5737[/TD]
[TD="class: xl66"]4852[/TD]
[TD="class: xl66"]345148[/TD]

[TD="class: xl65"]2[/TD]
[TD="class: xl70"]May-2016[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]5658[/TD]
[TD="class: xl66"]4932[/TD]
[TD="class: xl66"]340216[/TD]

[TD="class: xl65"]3[/TD]
[TD="class: xl70"]June-2016[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]5577[/TD]
[TD="class: xl66"]5012[/TD]
[TD="class: xl66"]335204[/TD]

[TD="class: xl65"]4[/TD]
[TD="class: xl70"]July-2016[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]5495[/TD]
[TD="class: xl66"]5095[/TD]
[TD="class: xl66"]330109[/TD]

[TD="class: xl65"]5[/TD]
[TD="class: xl70"]August-2016[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]5411[/TD]
[TD="class: xl66"]5178[/TD]
[TD="class: xl66"]324931[/TD]

[TD="class: xl65"]6[/TD]
[TD="class: xl70"]September-2016[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]5326[/TD]
[TD="class: xl66"]5263[/TD]
[TD="class: xl66"]319668[/TD]

[TD="class: xl65"]7[/TD]
[TD="class: xl70"]October-2016[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]5240[/TD]
[TD="class: xl66"]5349[/TD]
[TD="class: xl66"]314319[/TD]

[TD="class: xl65"]8[/TD]
[TD="class: xl70"]November-2016[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]5152[/TD]
[TD="class: xl66"]5437[/TD]
[TD="class: xl66"]308882[/TD]

[TD="class: xl65"]9[/TD]
[TD="class: xl70"]December-2016[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]5063[/TD]
[TD="class: xl66"]5526[/TD]
[TD="class: xl66"]303356[/TD]

[TD="class: xl65"]10[/TD]
[TD="class: xl70"]January-2017[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]4973[/TD]
[TD="class: xl66"]5617[/TD]
[TD="class: xl66"]297739[/TD]

[TD="class: xl65"]11[/TD]
[TD="class: xl70"]February-2017[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]4880[/TD]
[TD="class: xl66"]5709[/TD]
[TD="class: xl66"]292030[/TD]

[TD="class: xl65"]12[/TD]
[TD="class: xl70"]March-2017[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]4787[/TD]
[TD="class: xl66"]5802[/TD]
[TD="class: xl66"]286228[/TD]

[TD="class: xl65"]13[/TD]
[TD="class: xl70"]April-2017[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]4692[/TD]
[TD="class: xl66"]5897[/TD]
[TD="class: xl66"]280330[/TD]

[TD="class: xl65"]14[/TD]
[TD="class: xl70"]May-2017[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]4595[/TD]
[TD="class: xl66"]5994[/TD]
[TD="class: xl66"]274336[/TD]

[TD="class: xl65"]15[/TD]
[TD="class: xl70"]June-2017[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]4497[/TD]
[TD="class: xl66"]6092[/TD]
[TD="class: xl66"]268244[/TD]

[TD="class: xl65"]16[/TD]
[TD="class: xl70"]July-2017[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]4397[/TD]
[TD="class: xl66"]6192[/TD]
[TD="class: xl66"]262052[/TD]

[TD="class: xl65"]17[/TD]
[TD="class: xl70"]August-2017[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]4295[/TD]
[TD="class: xl66"]6294[/TD]
[TD="class: xl66"]255758[/TD]

[TD="class: xl65"]18[/TD]
[TD="class: xl70"]September-2017[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]4192[/TD]
[TD="class: xl66"]6397[/TD]
[TD="class: xl66"]249361[/TD]

[TD="class: xl65"]19[/TD]
[TD="class: xl70"]October-2017[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]4087[/TD]
[TD="class: xl66"]6502[/TD]
[TD="class: xl66"]242859[/TD]

[TD="class: xl65"]20[/TD]
[TD="class: xl70"]November-2017[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]3981[/TD]
[TD="class: xl66"]6608[/TD]
[TD="class: xl66"]236251[/TD]

[TD="class: xl65"]21[/TD]
[TD="class: xl70"]December-2017[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]3873[/TD]
[TD="class: xl66"]6717[/TD]
[TD="class: xl66"]229534[/TD]

[TD="class: xl65"]22[/TD]
[TD="class: xl70"]January-2018[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]3762[/TD]
[TD="class: xl66"]6827[/TD]
[TD="class: xl66"]222708[/TD]

[TD="class: xl65"]23[/TD]
[TD="class: xl70"]February-2018[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]3651[/TD]
[TD="class: xl66"]6939[/TD]
[TD="class: xl66"]215769[/TD]

[TD="class: xl65"]24[/TD]
[TD="class: xl70"]March-2018[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]3537[/TD]
[TD="class: xl66"]7052[/TD]
[TD="class: xl66"]208717[/TD]

[TD="class: xl65"]25[/TD]
[TD="class: xl70"]April-2018[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]3421[/TD]
[TD="class: xl66"]7168[/TD]
[TD="class: xl66"]201549[/TD]

[TD="class: xl65"]26[/TD]
[TD="class: xl70"]May-2018[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]3304[/TD]
[TD="class: xl66"]7285[/TD]
[TD="class: xl66"]194263[/TD]

[TD="class: xl65"]27[/TD]
[TD="class: xl70"]June-2018[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]3184[/TD]
[TD="class: xl66"]7405[/TD]
[TD="class: xl66"]186858[/TD]

[TD="class: xl65"]28[/TD]
[TD="class: xl70"]July-2018[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]3063[/TD]
[TD="class: xl66"]7526[/TD]
[TD="class: xl66"]179332[/TD]

[TD="class: xl65"]29[/TD]
[TD="class: xl70"]August-2018[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]2940[/TD]
[TD="class: xl66"]7650[/TD]
[TD="class: xl66"]171682[/TD]

[TD="class: xl65"]30[/TD]
[TD="class: xl70"]September-2018[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]2814[/TD]
[TD="class: xl66"]7775[/TD]
[TD="class: xl66"]163907[/TD]

[TD="class: xl65"]31[/TD]
[TD="class: xl70"]October-2018[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]2687[/TD]
[TD="class: xl66"]7902[/TD]
[TD="class: xl66"]156005[/TD]

[TD="class: xl65"]32[/TD]
[TD="class: xl70"]November-2018[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]2557[/TD]
[TD="class: xl66"]8032[/TD]
[TD="class: xl66"]147973[/TD]

[TD="class: xl65"]33[/TD]
[TD="class: xl70"]December-2018[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]2426[/TD]
[TD="class: xl66"]8164[/TD]
[TD="class: xl66"]139809[/TD]

[TD="class: xl65"]34[/TD]
[TD="class: xl70"]January-2019[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]2292[/TD]
[TD="class: xl66"]8297[/TD]
[TD="class: xl66"]131512[/TD]

[TD="class: xl65"]35[/TD]
[TD="class: xl70"]February-2019[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]2156[/TD]
[TD="class: xl66"]8433[/TD]
[TD="class: xl66"]123078[/TD]

[TD="class: xl65"]36[/TD]
[TD="class: xl70"]March-2019[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]2017[/TD]
[TD="class: xl66"]8572[/TD]
[TD="class: xl66"]114507[/TD]

[TD="class: xl65"]37[/TD]
[TD="class: xl70"]April-2019[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]1877[/TD]
[TD="class: xl66"]8712[/TD]
[TD="class: xl66"]105794[/TD]

[TD="class: xl65"]38[/TD]
[TD="class: xl70"]May-2019[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]1734[/TD]
[TD="class: xl66"]8855[/TD]
[TD="class: xl66"]96939[/TD]

[TD="class: xl65"]39[/TD]
[TD="class: xl70"]June-2019[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]1589[/TD]
[TD="class: xl66"]9000[/TD]
[TD="class: xl66"]87939[/TD]

[TD="class: xl65"]40[/TD]
[TD="class: xl70"]July-2019[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]1441[/TD]
[TD="class: xl66"]9148[/TD]
[TD="class: xl66"]78791[/TD]

[TD="class: xl65"]41[/TD]
[TD="class: xl70"]August-2019[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]1292[/TD]
[TD="class: xl66"]9298[/TD]
[TD="class: xl66"]69494[/TD]

[TD="class: xl65"]42[/TD]
[TD="class: xl70"]September-2019[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]1139[/TD]
[TD="class: xl66"]9450[/TD]
[TD="class: xl66"]60044[/TD]

[TD="class: xl65"]43[/TD]
[TD="class: xl70"]October-2019[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]984[/TD]
[TD="class: xl66"]9605[/TD]
[TD="class: xl66"]50439[/TD]

[TD="class: xl65"]44[/TD]
[TD="class: xl70"]November-2019[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]827[/TD]
[TD="class: xl66"]9762[/TD]
[TD="class: xl66"]40676[/TD]

[TD="class: xl65"]45[/TD]
[TD="class: xl70"]December-2019[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]667[/TD]
[TD="class: xl66"]9922[/TD]
[TD="class: xl66"]30754[/TD]

[TD="class: xl65"]46[/TD]
[TD="class: xl70"]January-2020[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]504[/TD]
[TD="class: xl66"]10085[/TD]
[TD="class: xl66"]20669[/TD]

[TD="class: xl67"]47[/TD]
[TD="class: xl71"]February-2020[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]339[/TD]
[TD="class: xl66"]10250[/TD]
[TD="class: xl66"]10418[/TD]

[TD="class: xl68"]48[/TD]
[TD="class: xl72"]March-2020[/TD]
[TD="class: xl66"]10589[/TD]
[TD="class: xl66"]171[/TD]
[TD="class: xl66"]10418[/TD]
[TD="class: xl66"]0[/TD]

</tbody>

 

[steve the fish]

Click in the cell showing March-2018. What does the formula bar say?

 

[Fluff]

Cross posted https://www.excelguru.ca/forums/showthread.php?8839-Lookup-Question

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.