How to use let/hstack to return all unique values that match to one criteria but not to another

Question

I want to find all the values where only one of two conditions is true and the other is false.

I have a table (Firm___Released_Job_Mats) which contains many things but impotantly for this formula it contains 1) the job names ([Job-Suff]), 2) the operation numbers ([opernum]) and 3) the materials used in the job ([item]). I want to make a list showing all of the materials used in one job (jorb1) that are not used in a second, comparison job (jorb2). When I use the following code to look up the materials they share, it works perfectly:

    =LET(
jorb1,JORB1,
jorb2,JORB2,
jobs,Firm___Released_Job_Mats[Job-Suff],
opernum,Firm___Released_Job_Mats[oper_num],
materials,Firm___Released_Job_Mats[item],
filt,FILTER(materials,jorb1=jobs),
unsharedmats,UNIQUE(FILTER(materials,(ISNUMBER(MATCH(materials,filt,0)))*(jobs=jorb2)*(opernum>19)*(opernum<28))),
unsharedoper,BYROW(unsharedmats,LAMBDA(a,INDEX(Firm___Released_Job_Mats[oper_num],MATCH(1,((a=Firm___Released_Job_Mats[item])*(Firm___Released_Job_Mats[Job-Suff]=jorb1)),0)))),
HSTACK(unsharedmats,unsharedoper))

but when I change the "jobs=jorb2" portion to "jobs<>jorb2" part (within the line defining "unsharedmats") to find the list of materials that are exclusive to the first job, it instead gives me a list of all materials, including the materials they share.

I have been able to recreate the desired solution using two helper columns (Y & Z) by having the following in column Y:

=CHOOSECOLS(FILTER(Firm___Released_Job_Mats,JORB1=Firm___Released_Job_Mats[Job-Suff],""),8)

(column 8 is the [item] column)

and this in column Z: =CHOOSECOLS(FILTER(Firm___Released_Job_Mats,JORB2=Firm___Released_Job_Mats[Job-Suff],""),8)

and then in column AA I can get the desired output with:

=FILTER(Z6#,NOT(ISNUMBER(MATCH(Z6#,Y6#,0))))

(data starts in row 6 for the helper columns)

But ideally I want to get this all into my let function and I'm not sure how I can accomplish that.

I have tried combining these into my let by doing this:

=LET(
jorb1,JORB1,
jorb2,JORB2,
jobs,Firm___Released_Job_Mats[Job-Suff],
opernum,Firm___Released_Job_Mats[oper_num],
materials,Firm___Released_Job_Mats[item],
filt1,CHOOSECOLS(FILTER(Firm___Released_Job_Mats,jorb1=jobs,""),8),
filt2,CHOOSECOLS(FILTER(Firm___Released_Job_Mats,jorb2=jobs,""),8),
unsharedmats,UNIQUE(FILTER(filt1,NOT(ISNUMBER(MATCH(filt1,filt2,0)))*(opernum>19)*(opernum<28))),
unsharedoper,BYROW(unsharedmats,LAMBDA(a,INDEX(Firm___Released_Job_Mats[oper_num],MATCH(1,((a=Firm___Released_Job_Mats[item])*(Firm___Released_Job_Mats[Job-Suff]=jorb1)),0)))),
HSTACK(unsharedmats,unsharedoper))

but it is returning #VALUE!

Here is the simplest example of what I'm looking for that I know how to offer:

Answer 1

It does not work because your operation is not filtered and not the same size as what is being passed to the MATCH:

=LET(
_rng,A1:C12,
jorb1,E1,
jorb2,F1,
jorb1_arr,FILTER(_rng,CHOOSECOLS(_rng,1)=jorb1),
jorb1_m,CHOOSECOLS(jorb1_arr,2),
jorb1_op,CHOOSECOLS(jorb1_arr,3),
jorb2_arr,FILTER(_rng,CHOOSECOLS(_rng,1)=jorb2),
jorb2_m,CHOOSECOLS(jorb2_arr,2),
jorb2_op,CHOOSECOLS(jorb2_arr,3),
jorb1_us,FILTER(CHOOSECOLS(jorb1_arr,2,3),(ISERROR(MATCH(jorb1_m,jorb2_m,0)))*(jorb1_op>19)*(jorb1_op<28),{"",""}),
jorb2_us,FILTER(CHOOSECOLS(jorb2_arr,2,3),(ISERROR(MATCH(jorb2_m,jorb1_m,0)))*(jorb2_op>19)*(jorb2_op<28),{"",""}),
VSTACK(HSTACK(jorb1,""),HSTACK("Material","Operation"),jorb1_us,HSTACK("Material","Operation"),HSTACK(jorb2,""),jorb2_us))