SOLVED

# How to get row data if matching specific column criteria

Occasional Contributor

# How to get row data if matching specific column criteria

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

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

# Re: How to get row data if matching specific column criteria

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

# Re: How to get row data if matching specific column criteria

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

# Re: How to get row data if matching specific column criteria

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.

# Re: How to get row data if matching specific column criteria

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

# Re: How to get row data if matching specific column criteria

Perfect ! Thank you again for your help

# Re: How to get row data if matching specific column criteria

@FaisalExcell You're welcome!

# Re: How to get row data if matching specific column criteria

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

# Re: How to get row data if matching specific column criteria

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

# Re: How to get row data if matching specific column criteria

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

# Re: How to get row data if matching specific column criteria

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

# Re: How to get row data if matching specific column criteria

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

# Re: How to get row data if matching specific column criteria

@FaisalExcell Try this:

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