Vlookup option for Table

So I have 2 tables with a 1 to many relationship. On the 1 or lookup table ClinicalBODash I'm trying to add a column from the many table PM by PN. PM by PN is a list of all the part numbers in my system while ClinicalBODash has only clinical part numbers. Both tables have unique part numbers denoted as Material, and those are the related columns in the table. I heard that the related function doesn't work when you try to use it on the upstream table, in this case, ClinicalBODash and I've tried LookupValue to no success. Is there a way to retreive the prod.heirarchy results and match them to material?