SOLVED

How to get row data if matching specific column criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-1966490%22%20slang%3D%22en-US%22%3EHow%20to%20get%20row%20data%20if%20matching%20specific%20column%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1966490%22%20slang%3D%22en-US%22%3E%3CP%3EIm%20really%20struggling%20on%20writing%20the%20correct%20formula%20here%20appreciate%20if%20i%20could%20get%20a%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22pp.PNG%22%20style%3D%22width%3A%20927px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F238621i611BDC09AE844BF4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22pp.PNG%22%20alt%3D%22pp.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1966490%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1966552%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20row%20data%20if%20matching%20specific%20column%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1966552%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F894518%22%20target%3D%22_blank%22%3E%40FaisalExcell%3C%2FA%3E%26nbsp%3BSee%20a%20mock-up%20of%20your%20schedule%20attached%2C%20offering%20three%20different%20approaches.%20VLOOKUP%2C%20XLOOKUP%20and%20)NDEX%2FMATCH.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1966727%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20row%20data%20if%20matching%20specific%20column%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1966727%22%20slang%3D%22en-US%22%3EYou%20are%20the%20Best!!!!%20thank%20you%20my%20dear%20for%20your%20quick%20respond%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1997062%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20row%20data%20if%20matching%20specific%20column%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1997062%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20have%20used%20the%20VLOOKUP%2C%20but%20now%20i%20found%20a%20problem%20when%20i%20drag%20to%20fill%20to%20the%20next%20cell%3CSPAN%3E%26nbsp%3Bhorizontally.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Eif%20the%20VLOOKUP%26nbsp%3Bis%20first%20entered%20in%20cell%20F5%20as%20below%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DVLOOKUP(%24D5%2C'Competency%20Requirement'!%24C%245%3A%24KS%24100%2C%3CSTRONG%3E2%3C%2FSTRONG%3E%2CFALSE)%3CBR%20%2F%3E%3CBR%20%2F%3EThen%26nbsp%3Bi%20would%20like%20it%20to%20become%20as%20below%20if%20I%20drag%20it%20horizontally%20to%20G5%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DVLOOKUP(%24D5%2C'Competency%20Requirement'!%24C%245%3A%24KS%24100%2C%3CSTRONG%3E3%3C%2FSTRONG%3E%2CFALSE)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Ethe%20same%20for%20the%20next%20till%20end%20of%20the%20training%20list.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Ei%20have%20attached%20the%20file%20for%20better%20look.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1997108%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20row%20data%20if%20matching%20specific%20column%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1997108%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F894518%22%20target%3D%22_blank%22%3E%40FaisalExcell%3C%2FA%3E%26nbsp%3BBest%20to%20add%20a%20MATCH%20function%20to%20make%20the%20column%20definition%20dynamic%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DVLOOKUP(%24D5%2C'Competency%20Requirement'!%24C%245%3A%24KS%24100%2CMATCH(F%243%2C'Competency%20Requirement'!%24C%243%3A%24KS%243%2C0)%2CFALSE)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ETo%20make%20it%20all%20easier%20to%20read%20and%20maintain%2C%20however%2C%20consider%20creating%20named%20ranges%20for%20the%20lookup%20range%20(e.g.%20CompReq)%26nbsp%3Band%20the%20column%20headers%20(e.g.%20TRheader).%20Then%2C%20the%20formula%20could%20look%20a%20lot%20more%20user-friendly%2C%20like%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DVLOOKUP(%24D5%2CCompReq%2CMATCH(F%243%2CTRheader%2C0)%2CFALSE)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EDid%20just%20that%20in%20your%20original%20file.%20See%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1997185%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20row%20data%20if%20matching%20specific%20column%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1997185%22%20slang%3D%22en-US%22%3EPerfect%20!%20Thank%20you%20again%20for%20your%20help%3C%2FLINGO-BODY%3E
Occasional Contributor

Im really struggling on writing the correct formula here appreciate if i could get a help

 

pp.PNG

12 Replies
best response confirmed by FaisalExcell (Occasional Contributor)
Solution

@FaisalExcell See a mock-up of your schedule attached, offering three different approaches. VLOOKUP, XLOOKUP and INDEX/MATCH.

You are the Best!!!! thank you my dear for your quick respond

Hi @Riny_van_Eekelen 

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.

 

@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.

Perfect ! Thank you again for your help

Hey @Riny_van_Eekelen i promise this will be my last

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 

@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.

 

 

Hi @Riny_van_Eekelen  but that will get me the information of the requirement F5, i want to get the dates from F6.

@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.

@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 ?

@FaisalExcell Try this:

=IF(C9#="NA","--",TRANSPOSE(XLOOKUP(C4,'Employee Training Matrix'!A5:A164,'Employee Training Matrix'!F6:KS165)))