SOLVED

Make List from every column that has data corresponding to name

%3CLINGO-SUB%20id%3D%22lingo-sub-3115561%22%20slang%3D%22en-US%22%3EMake%20List%20from%20every%20column%20that%20has%20data%20corresponding%20to%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3115561%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20i%20have%20a%20problem%20figuring%20out%20how%20to%20make%20list%20like%20this%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JohnHoego_0-1643960217062.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345249i402D1CA27536BC85%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JohnHoego_0-1643960217062.png%22%20alt%3D%22JohnHoego_0-1643960217062.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EFrom%20a%20table%20like%20this%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JohnHoego_1-1643960338335.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345250i7FDD4D7A3CB58770%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JohnHoego_1-1643960338335.png%22%20alt%3D%22JohnHoego_1-1643960338335.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ei%20need%20a%20formula%20that%20can%20list%20only%20the%20job%20person%20does%20i%20have%20tried%20%22vlookup%22%20and%20%22hlookup%22%20but%20no%20luck%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3115561%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3116345%22%20slang%3D%22en-US%22%3ERe%3A%20Make%20List%20from%20every%20column%20that%20has%20data%20corresponding%20to%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3116345%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1296941%22%20target%3D%22_blank%22%3E%40JohnHoego%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20layout%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20765px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345272i97DFE1F9F0A47DB6%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eit%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(%0A%20%20%20name%2C%20J4%2C%0A%20%20%20personAmounts%2C%20XLOOKUP(%20name%2C%20jobs%5BName%5D%2Cjobs%5B%5Bbending%5D%3A%5Bassembling%5D%5D%2C%20%22%22)%2C%0A%20%20%20personJobs%2C%20%20%20%20FILTER(%20jobs%5B%5B%23Headers%5D%2C%5Bbending%5D%3A%5Bassembling%5D%5D%2C%20personAmounts)%2C%0A%20%20IF(%20%7B1%2C0%7D%2C%20TRANSPOSE(%20personJobs%20)%2C%20TRANSPOSE(%20FILTER(%20personAmounts%2C%20personAmounts%20)%20)%20)%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eif%20you%20are%20on%20Excel%20365%20or%202021%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3126175%22%20slang%3D%22en-US%22%3ERe%3A%20Make%20List%20from%20every%20column%20that%20has%20data%20corresponding%20to%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3126175%22%20slang%3D%22en-US%22%3Ethankyou%20so%20much%20for%20this!%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi i have a problem figuring out how to make list like this 

JohnHoego_0-1643960217062.png

From a table like this

JohnHoego_1-1643960338335.png

i need a formula that can list only the job person does i have tried "vlookup" and "hlookup" but no luck

5 Replies
best response confirmed by JohnHoego (New Contributor)
Solution

@JohnHoego 

For such layout

image.png

it could be

=LET(
   name, J4,
   personAmounts, XLOOKUP( name, jobs[Name],jobs[[bending]:[assembling]], ""),
   personJobs,    FILTER( jobs[[#Headers],[bending]:[assembling]], personAmounts),
  IF( {1,0}, TRANSPOSE( personJobs ), TRANSPOSE( FILTER( personAmounts, personAmounts ) ) )  )

if you are on Excel 365 or 2021

thankyou so much for this!
can you explain more about the IF({1,0}) parts? i've been trying to add a third column with a different value but it seems the value always the same for 1- infinity

@JohnHoego 

For more than 2 columns it's better to use something like

image.png

function iterates condition array and returns selected value (which is actually array or column) on correspondent position. It could be modified as needed, for example

image.png

IF() may return only two values / columns (if true and if false), but from these two you may combine as many columns as needed

image.png

You have no way to add here one more column, only combine C3:C7 and E3:E7 as desired.