Form value populated based on previous selection

Hi, I need a form value to populate based on the previous selection from a dropdown.

I have the following 3 columns; Product - Product Code - Product Description within an excel sheet. The Product column is populated through the concatenation of Product Code and Description. You can see an example of this below.

I have multiple sheets with the same column information, except the product groups are different. In the form I select my Product Group which then allows me to select from a list of Products associated to that group; the dropdown shows the Product column from the spreadsheet i.e. the concatenated values.

Underneath the product group and product dropdown items in the form, I want to populate the following ‘Product Code’ form value with the product code associated with the product they have chosen. So, much like I have filtered the product groups and products, I want the product code for that selected row to appear in the form. For example, if a user selects ‘HD1/10W-Hep20 Pb Staright Connector WT 10’ the product code ‘HD1/10W’ will appear in the product code form input.

I have attempted this using various SELECT() and FILTER() statements, but because the product groups are across various sheets in excel I cannot find an expression that is universal to the situation; I have to refer to a specific dataset(sheet) in excel but this process must work across all groups of products.

If there are any possible approaches to this, I would be appreciative of any advice.

Thanks

To understand better your table/column structure, what formula are you using when selecting the Group and Product?

When selecting the Product Group: Groups[Group]
This returns a dropdown based on a list of values in ‘Groups’ excel sheet.

When selecting a Product from a group: I am using an IFS statement. Like the example below;
IFS(
[Product Group] = Malleable Black Iron Fittings ,Malleable Black Iron Fittings[Product],
[Product Group] = Hep2O Pipe and Fittings,Hep2O Pipe and Fittings[Product]
)

I have made some progress populating the Product code using the LEFT() expression, however I cannot specify explicitly how many characters from the left fragment I wish to show as all Product Codes are of different lengths.

I tried to use LEFT([Product],LEN([Product Code])) in an attempt to grab the Product Code concatenated in Product, based on the length of the Product Code. However, this method removed Product Code from the form.

You can use SPLIT expression like INDEX(SPLIT([Column],"-"),1) to read your first part.

Thank you! Using Split in this way worked; I was previously attempting to do it on a Text type instead of list type I think that was the issue.

The SPLIT expression is meant for string to create a list… so it should work with the text.