The correct formula is TEXT(Galaxy__r.Type__c) .

The parent field Type__c is a picklist. In Salesforce formulas, a picklist value cannot be displayed directly as plain text unless it is converted with the TEXT() function. Salesforce documentation states that TEXT() converts picklist values to text in supported formula contexts. Cross-object formulas can reference fields from related parent records, so the child object Star__c can reference the parent Galaxy__c field using the relationship syntax Galaxy__r.Type__c .

VALUE() is for converting numeric text into a number, ISPICKVAL() checks whether a picklist equals a specific value, and FIND() searches text. None of those displays the parent picklist value as text.

===========