Brian,

If I understand you correctly, what you're trying to do is to find the appropriate Lead Subtype to assign to a new Lead based on a "primary key" made up of RecordTypeId__c and SourceDetailName__c. So, what you really want is to put all of your SourceDetail__c records into a map keyed by this custom "primary key". Here's what I mean:

// Prepare a Map of SourceDetail__c records
// keyed by a custom "primary key":
// Primary Key = RecordTypeId__c + '/' + SourceDetailName__c ;
Map<String,SourceDetail__c> mSourceDetails 
   = new Map<String,SourceDetail__c>();

// Loop through all of your SourceDetail__c records
// and 'key' them into our map
for (List<SourceDetail__c> sourceDetails : 
       [SELECT RecordTypeId__c, LeadSubtype__c, SourceDetailName__c 
        FROM SourceDetail__c]) {
   for (SourceDetail__c sd : sourceDetails) {
       // Construct the primaryKey
       String primaryKey = sd.RecordTypeId__c + '/' + sd.SourceDetailName__c;
       // Add this SourceDetail__c record to our map, 
       //    keyed by its composite Primary Key
       mSourceDetails.put(primaryKey,sd);
   }
}

// NOW, you can do something like this:
for (Lead l : Trigger.New) {
   // Construct the Lead's 'Primary Key',
   // so that we can try to find a matching record in mSourceDetails
   String primaryKey = l.RecordTypeId + '/' + l.Lead_Source_Detail__c;
   if (mSourceDetails.containsKey(primaryKey)) {
      SourceDetail__c sd = mSourceDetails.get(primaryKey);
      // Populate the new Lead's 'Subtype' field
      //    using the matching Source Detail record's 'LeadSubtype__c' field
      l.Subtype = sd.LeadSubtype__c;
   }
}

To make this more efficient in a Trigger, you'd want to only grab those SourceDetail__c records that you actually need by doing an initial loop through the Leads in Trigger.new to see which Lead Record Types and Lead Source Details are actually being inserted. For example, here would be a complete Trigger implementation:

trigger Leads on Lead (before insert) {

	if (Trigger.isBefore && Trigger.isInsert) {
	
		Set<String> recordTypeIds = new Set<String>();
		Set<String> leadSourceDetailNames = new Set<String>();
		
		for (Lead l : Trigger.new) {
			if (!recordTypeIds.contains(l.RecordTypeId)) {
				recordTypeIds.add(l.RecordTypeId);
			}
			if (l.Lead_Source_Detail__c != NULL
				&& !leadSourceDetailNames.contains(l.Lead_Source_Detail__c)) {
				leadSourceDetailNames.add(l.Lead_Source_Detail__c);
			}	
		}
		
		// Prepare a Map of SourceDetail__c records
		// keyed by a custom "primary key":
		// Primary Key = RecordTypeId__c + '/' + SourceDetailName__c ;
		Map<String,SourceDetail__c> mSourceDetails 
		   = new Map<String,SourceDetail__c>();

		// Loop through all of your SourceDetail__c records
		// and 'key' them into our map
		for (List<SourceDetail__c> sourceDetails : 
			   [SELECT RecordTypeId__c, LeadSubtype__c, SourceDetailName__c 
				FROM SourceDetail__c
				WHERE RecordTypeId__c in :recordTypeIds
				AND SourceDetailName__c in :leadSourceDetailNames]) {
		   for (SourceDetail__c sd : sourceDetails) {
			   // Construct the primaryKey
			   String primaryKey = sd.RecordTypeId__c + '/' + sd.SourceDetailName__c;
			   // Add this SourceDetail__c record to our map, 
			   //    keyed by its composite Primary Key
			   mSourceDetails.put(primaryKey,sd);
		   }
		}
		
		if (!mSourceDetails.isEmpty()) {
		
			for (Lead l : Trigger.New) {
			
			   // Construct the Lead's 'Primary Key',
			   // so that we can try to find a matching record in mSourceDetails
			   String primaryKey = l.RecordTypeId + '/' + l.Lead_Source_Detail__c;
			   
			   if (mSourceDetails.containsKey(primaryKey)) {
			   
			      // Get the SourceDetail__c record with this Primary Key
				  SourceDetail__c sd = mSourceDetails.get(primaryKey);
				  
				  // Populate the new Lead's 'Subtype' field
				  //    using the matching Source Detail record's 'LeadSubtype__c' field
				  l.Subtype = sd.LeadSubtype__c;
				  
			   }
			}
		
		}
	
	} // end if (Trigger.isBefore && Trigger.isInsert)

} // end trigger