Look up in 2 columns and return to date

[JDJong]

Hi all,

I am struggling with the formula. I tried using match index, but it wouldn't work.

So i have attached a workbook, basically to look up the data in column A course number and column E course type, when matched, extract the date start and date end to the results sheet.

Hopefully you guys can help me out.!

Thank you very much !

Result would be:

[TABLE="width: 696"]
<tbody>[TR]
[TD]Course code[/TD]
[TD]Course type[/TD]
[TD]date start[/TD]
[TD]date end[/TD]
[TD][/TD]
[TD][/TD]
[TD]Course code[/TD]
[TD]Course type[/TD]
[TD]date start[/TD]
[TD]date end[/TD]
[/TR]
[TR]
[TD="align: right"]8776789[/TD]
[TD]CC[/TD]
[TD="align: right"]3-5-2018[/TD]
[TD="align: right"]2-6-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]8767UI24[/TD]
[TD]AC[/TD]
[TD="align: right"]7-5-2018[/TD]
[TD="align: right"]7-6-2018[/TD]
[/TR]
</tbody><colgroup><col><col><col span="4"><col><col><col><col></colgroup>[/TABLE]

Lookup table:
[TABLE="width: 995"]
<tbody>[TR]
[TD]Course No.[/TD]
[TD]Course code[/TD]
[TD]Descript[/TD]
[TD]Amount[/TD]
[TD]Type[/TD]
[TD]date start[/TD]
[TD]date end[/TD]
[/TR]
[TR]
[TD="align: right"]8776789[/TD]
[TD]0098AC0000[/TD]
[TD]course_charge_03.05-02.06.2018_CN_8776789[/TD]
[TD] $ 45.00 [/TD]
[TD]CC[/TD]
[TD="align: right"]3-5-2018[/TD]
[TD="align: right"]2-6-2018[/TD]
[/TR]
[TR]
[TD="align: right"]8776789[/TD]
[TD]0098AC0001[/TD]
[TD]course_charge_03.05-02.06.2018_CN_8776789[/TD]
[TD] $ 29.45 [/TD]
[TD]CC[/TD]
[TD="align: right"]3-5-2018[/TD]
[TD="align: right"]2-6-2018[/TD]
[/TR]
[TR]
[TD]8767UI9[/TD]
[TD]0098AC0002[/TD]
[TD]course_charge_03.05-02.06.2018_CN_8767U19[/TD]
[TD] $ 63.5 [/TD]
[TD]CC[/TD]
[TD="align: right"]3-5-2018[/TD]
[TD="align: right"]2-6-2018[/TD]
[/TR]
[TR]
[TD]8767UI10[/TD]
[TD]0098AC0003[/TD]
[TD]course_charge_03.05-02.06.2018_CN_8767U110[/TD]
[TD] $ 58.900,00 [/TD]
[TD]CC[/TD]
[TD="align: right"]3-5-2018[/TD]
[TD="align: right"]2-6-2018[/TD]
[/TR]
[TR]
[TD]8767UI11[/TD]
[TD]00DC000111[/TD]
[TD]course_charge_03.05-02.06.2018_CN_8767U111[/TD]
[TD] $ 72.769,40 [/TD]
[TD]CC[/TD]
[TD="align: right"]3-5-2018[/TD]
[TD="align: right"]2-6-2018[/TD]
[/TR]
[TR]
[TD]8767UI12[/TD]
[TD]00DC000112[/TD]
[TD]course_charge_03.05-02.06.2018_CN_8767U112[/TD]
[TD] $ 80.646,50 [/TD]
[TD]CC[/TD]
[TD="align: right"]3-5-2018[/TD]
[TD="align: right"]2-6-2018[/TD]
[/TR]
[TR]
[TD]8767UI13[/TD]
[TD]00DC000113[/TD]
[TD]course_charge_03.05-02.06.2018_CN_8767U113[/TD]
[TD] $ 78.771,00 [/TD]
[TD]CC[/TD]
[TD="align: right"]3-5-2018[/TD]
[TD="align: right"]2-6-2018[/TD]
[/TR]
[TR]
[TD]8767UI14[/TD]
[TD]00DC000114[/TD]
[TD]course_charge_03.05-02.06.2018_CN_8767U114[/TD]
[TD] $ 82.522,00 [/TD]
[TD]CC[/TD]
[TD="align: right"]3-5-2018[/TD]
[TD="align: right"]2-6-2018[/TD]
[/TR]
[TR]
[TD]8767UI15[/TD]
[TD]00DC000115[/TD]
[TD]applicance_charge_07.05-06.06.2018_AC_8767U115[/TD]
[TD] $ 77.500,00 [/TD]
[TD]AC[/TD]
[TD="align: right"]7-5-2018[/TD]
[TD="align: right"]7-6-2018[/TD]
[/TR]
[TR]
[TD]8767UI16[/TD]
[TD]00DC000116[/TD]
[TD]applicance_charge_07.05-06.06.2018_AC_8767U116[/TD]
[TD] $ 77.500,00 [/TD]
[TD]AC[/TD]
[TD="align: right"]7-5-2018[/TD]
[TD="align: right"]7-6-2018[/TD]
[/TR]
[TR]
[TD]8767UI17[/TD]
[TD]00UU000111[/TD]
[TD]applicance_charge_07.05-06.06.2018_AC_8767U117[/TD]
[TD] $ 77.500,00 [/TD]
[TD]AC[/TD]
[TD="align: right"]7-5-2018[/TD]
[TD="align: right"]7-6-2018[/TD]
[/TR]
[TR]
[TD]8767UI18[/TD]
[TD]00DC000117[/TD]
[TD]applicance_charge_07.05-06.06.2018_AC_8767U118[/TD]
[TD] $ 73.625,00 [/TD]
[TD]AC[/TD]
[TD="align: right"]7-5-2018[/TD]
[TD="align: right"]7-6-2018[/TD]
[/TR]
[TR]
[TD]8767UI19[/TD]
[TD]00DC000118[/TD]
[TD]applicance_charge_07.05-06.06.2018_AC_8767U119[/TD]
[TD] $ 69.750,00 [/TD]
[TD]AC[/TD]
[TD="align: right"]7-5-2018[/TD]
[TD="align: right"]7-6-2018[/TD]
[/TR]
[TR]
[TD]8767UI20[/TD]
[TD]00DC000119[/TD]
[TD]applicance_charge_07.05-06.06.2018_AC_8767U120[/TD]
[TD] $ 27.964,60 [/TD]
[TD]AC[/TD]
[TD="align: right"]7-5-2018[/TD]
[TD="align: right"]7-6-2018[/TD]
[/TR]
[TR]
[TD]8767UI21[/TD]
[TD]00DC000120[/TD]
[TD]applicance_charge_07.05-06.06.2018_AC_8767U121[/TD]
[TD] $ 49.954,60 [/TD]
[TD]AC[/TD]
[TD="align: right"]7-5-2018[/TD]
[TD="align: right"]7-6-2018[/TD]
[/TR]
[TR]
[TD]8767UI22[/TD]
[TD]00DC000121[/TD]
[TD]applicance_charge_07.05-06.06.2018_AC_8767U122[/TD]
[TD] $ 200.000,00 [/TD]
[TD]AC[/TD]
[TD="align: right"]7-5-2018[/TD]
[TD="align: right"]7-6-2018[/TD]
[/TR]
[TR]
[TD]8767UI23[/TD]
[TD]00DC000122[/TD]
[TD]applicance_charge_07.05-06.06.2018_AC_8767U123[/TD]
[TD] $ 83.700,00 [/TD]
[TD]AC[/TD]
[TD="align: right"]7-5-2018[/TD]
[TD="align: right"]7-6-2018[/TD]
[/TR]
[TR]
[TD]8767UI24[/TD]
[TD]00DC000123[/TD]
[TD]applicance_charge_07.05-06.06.2018_AC_8767U124[/TD]
[TD] $ 52.000,00 [/TD]
[TD]AC[/TD]
[TD="align: right"]7-5-2018[/TD]
[TD="align: right"]7-6-2018[/TD]
[/TR]
[TR]
[TD]8767UI25[/TD]
[TD]00DC000124[/TD]
[TD]applicance_charge_07.05-06.06.2018_AC_8767U125[/TD]
[TD] $ 71.300,00 [/TD]
[TD]AC[/TD]
[TD="align: right"]7-5-2018[/TD]
[TD="align: right"]7-6-2018[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[BobUmlas]

Ctrl+Shift+enter:
=INDEX(F:F,MATCH(Cell1&Cell2, $A$1:$A$1000&$E$1:$E$1000,0))

 

[JDJong]

Hey Bobumlas,

Thanks for your quick Response. I have been provided with =IFERROR(INDEX('lookup table'!$F$2:$F$20;MATCH(results!$A$2&results!$B$2;'lookup table'!$A$2:$A$20&'lookup table'!$E$2:$E$20;0));"") which works great!

I am trying to set conditional formating for those period that doesn't fall into the whole month such as 5.5 - 4.6 or 15.5 - 14.6