Forum Discussion
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
FaisalExcell See a mock-up of your schedule attached, offering three different approaches. VLOOKUP, XLOOKUP and INDEX/MATCH.
12 Replies
FaisalExcell See a mock-up of your schedule attached, offering three different approaches. VLOOKUP, XLOOKUP and INDEX/MATCH.
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.
- You are the Best!!!! thank you my dear for your quick respond
