Dec 08 2020 03:44 AM
Im really struggling on writing the correct formula here appreciate if i could get a help
Dec 08 2020 03:59 AM - edited Dec 08 2020 04:00 AM
Solution@FaisalExcell See a mock-up of your schedule attached, offering three different approaches. VLOOKUP, XLOOKUP and INDEX/MATCH.
Dec 08 2020 04:37 AM
Dec 17 2020 03:41 AM
I have used the VLOOKUP, but now i found a problem when i drag to fill to the next cell horizontally.
if the VLOOKUP is first entered in cell F5 as below
=VLOOKUP($D5,'Competency Requirement'!$C$5:$KS$100,2,FALSE)
Then i would like it to become as below if I drag it horizontally to G5
=VLOOKUP($D5,'Competency Requirement'!$C$5:$KS$100,3,FALSE)
the same for the next till end of the training list.
i have attached the file for better look.
Dec 17 2020 04:05 AM
@FaisalExcell Best to add a MATCH function to make the column definition dynamic:
=VLOOKUP($D5,'Competency Requirement'!$C$5:$KS$100,MATCH(F$3,'Competency Requirement'!$C$3:$KS$3,0),FALSE)
To make it all easier to read and maintain, however, consider creating named ranges for the lookup range (e.g. CompReq) and the column headers (e.g. TRheader). Then, the formula could look a lot more user-friendly, like:
=VLOOKUP($D5,CompReq,MATCH(F$3,TRheader,0),FALSE)
Did just that in your original file. See attached.
Dec 17 2020 04:37 AM
Dec 17 2020 04:52 AM
@FaisalExcell You're welcome!
Feb 25 2021 01:58 AM
Hey @Riny_van_Eekelen i promise this will be my last :D
in the Employee Profile tab I'm trying to get the confirmation date from Employee Training Matrix, i managed by luck to get the requirement need but when i applied the same used formula to the date i get #VALUE
Feb 25 2021 02:14 AM
@FaisalExcell In the last part of the formula ('Employee Training Matrix'!F6:KS164)), you should change the first cell in the return_array to F5 so that the size matched the size of the lookup_array.
Feb 25 2021 02:24 AM
Hi @Riny_van_Eekelen but that will get me the information of the requirement F5, i want to get the dates from F6.
Feb 25 2021 06:52 AM
@FaisalExcell Aha, I see. Try this one in stead:
=TRANSPOSE(XLOOKUP(C4,'Employee Training Matrix'!A5:A164,'Employee Training Matrix'!F6:KS165))
The Lookup_array is in row 5:164 and return array in row 6:165. Still the same array size, but shifted one row between them.
Feb 28 2021 03:46 AM
@Riny_van_Eekelen Thanx Riny i have figured that out during the weekend.
=TRANSPOSE(XLOOKUP(C4,'Employee Training Matrix'!A5:A164,'Employee Training Matrix'!F6:KS165))
but the empty cells are showing 0 when transposing, how can i add IF in middle of the formula to Show "--" if empty cells ?
Feb 28 2021 04:30 AM
@FaisalExcell Try this:
=IF(C9#="NA","--",TRANSPOSE(XLOOKUP(C4,'Employee Training Matrix'!A5:A164,'Employee Training Matrix'!F6:KS165)))
Dec 08 2020 03:59 AM - edited Dec 08 2020 04:00 AM
Solution@FaisalExcell See a mock-up of your schedule attached, offering three different approaches. VLOOKUP, XLOOKUP and INDEX/MATCH.