Very Challenging Scenario, Reading Excel and Lookup

[Anonymous]

Hi Guys,
I have a scenario and hope you can help me with this:
Input:
 REGULATORY_AGENCIES   |   LEAD_AGENCY       <---- this is a Field Header
 10001; 10002                  |   10003; 10001        <---- value
Lookup (in excel)
Field_Header                    |     CODE        |    CODE_DESCRIPTION
REGULATORY_AGENCIES     |     10001       |     Apple
REGULATORY_AGENCIES     |     10002       |     Banana
REGULATORY_AGENCIES     |     10003       |     Carrot 
LEAD_AGENCY                   |     10001       |     Apple Juice
LEAD_AGENCY                   |     10002       |     Banana Juice
LEAD_AGENCY                   |     10003       |     Carrot Juice
Expected Output:
REGULATORY_AGENCIES   |   LEAD_AGENCY                        <---- this is a Field Header
 Apple; Banana                  |   Carrot Juice; Apple Juice        <---- value
In this example, I need my Input to lookup to the lookup table and return its description. I have already created a Routines functions with this that reads a excel file (my lookup table) and return its value. BUT I have only relate CODE and CODE_DESCRIPTION. thats why I'm getting multiple values:
REGULATORY_AGENCIES                                 |   LEAD_AGENCY                                          <---- this is a Field Header
 Apple; Apple Juice; Banana; Banana Juice         |   Carrot; Carrot Juice; Apple; Apple Juice        <---- value
My question is, how can I use or read my HEADER in Input so that I can relate it to the FIELD_HEADER in the lookup table? Here is my code:

public static String splitColumnHandling(String Field)
{
String str = Field;
		String ValueHolder="";
		String[] myarray = str.split("; ");
		String a="";
		String b="";
		
		try
		{
		//Create Workbook instance holding reference to .xlsx file
		FileInputStream file = new FileInputStream(new File("D:\\MY PROJECT\\lookup_table.xlsx"));
		XSSFWorkbook workbook = new XSSFWorkbook(file);
		 
			//Get first/desired sheet from the workbook
				XSSFSheet sheet = workbook.getSheetAt(0);
				//begin loop spliting 
				for(int i = 0; i < myarray.length; i++)
				{
					a=myarray.toString();	 
					 //Iterate through each rows one by one
					Iterator<Row> rowIterator = sheet.iterator();
					for(int x = 0; x < sheet.getPhysicalNumberOfRows(); x++)
					{
						//Declare cell position to be read
						Row row = rowIterator.next();
						CellReference pos_return_value = new CellReference("D1");
						CellReference pos_lookup_codes = new CellReference("C1");
						Cell get_return_value = row.getCell(pos_return_value.getCol());
						Cell get_lookup_codes = row.getCell(pos_lookup_codes.getCol());
						
							if(a.equals(get_lookup_codes.getStringCellValue()))
							{	
								if(i!=(myarray.length-1)){
									ValueHolder=ValueHolder+get_return_value+"; ";
								}else{
									ValueHolder=ValueHolder+get_return_value+"";
								}	
							}
							else if(a.equals("NONE SPECIFIED") )
							{
								ValueHolder="NONE SPECIFIED";
							}
					}
					b=b+"\n"+a; 
				} 
			
		if (ValueHolder.isEmpty()){
				ValueHolder=Field;
			}
		}catch(Exception e )
		{}
		return String.valueOf(ValueHolder);
	}	
}//end

Thank you so much and hope you can help me with it

[Anonymous]

Hi 
Is the Filed Header a fixed value or it might change for each run? If the Field Header are always a constant, you can convert the input data to the below output with tNormalize component. 

and then, you are able to do join with the lookup table based on Field_Header and Code columns.
Regards
Shong