Provide me with all main products (which are not present in the sub products) with their lowest

srivatsa224 Further to my previous response, if the sample data provided is not a complete and accurate representation of your actual data, there are certain scenarios that can return errors. For example, if the sub product combo "B / F" does not always appear at the "Ground" level, or if "B" can also be combined with other sub products such as "B / K".

Another option would be to use the SCAN function to assign ID numbers to each product grouping; however, this method requires the data to be arranged so that each product series appear together and in order from Sub1, Sub2, etc. to Ground. For example, your sample data shows the first record "A / B / Sub1" is separated from its corresponding "B / F / Ground" record by two other records. "A / B / Sub1" would need to be followed by "B / F / Ground" with nothing else in between them. If this is always true for your actual data, then the following formula should work:

=LET(
    data, A2:D16,
    id, SCAN(0, SEQUENCE(ROWS(data)), LAMBDA(v,n, IFS(n=1, 1, INDEX(data, n-1, 3)="Ground", v+1, TRUE, v))),
    DROP(REDUCE("", UNIQUE(id), LAMBDA(v,n, LET(
        arr, FILTER(data, id=n),
        VSTACK(v, HSTACK(TAKE(arr, 1, 1), CHOOSECOLS(TAKE(arr, -1), 2, 3), PRODUCT(CHOOSECOLS(arr, 4))))))), 1)
)

Results

Please see the attached file...