Forum Discussion

JohnHoego's avatar
JohnHoego
Copper Contributor
Feb 03, 2022
Solved

Make List from every column that has data corresponding to name

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

From a table like this

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

excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 04, 2022

    JohnHoego 

    For such layout

    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

5 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 04, 2022

    JohnHoego 

    For such layout

    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