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:

Course code	Course type	date start	date end			Course code	Course type	date start	date end
8776789	CC	3-5-2018	2-6-2018			8767UI24	AC	7-5-2018	7-6-2018

<tbody>
</tbody><colgroup><col><col><col span="4"><col><col><col><col></colgroup>

Lookup table:

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

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>

 

[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