excelindexingexcel-formulamatchlookup-tables

Need to lookup value in multiple columns, return value in another column each time the first value appears


Truck Number Job1 Job2 Job3
71 5928 5928 5928
72 3958 5928 2971
73 2971 5928 2971

This table is filled in automatically from our daily dispatch sheet to break down a driver's route. In my table, each truck number gets a row, and each job they complete gets a column. I need to be able to fill in a separate table where the job number is the column header (these are be permanent values) with each truck number that ran that job below. the truck number needs to be returned as many times as the job number appears.

For this example, this data set should return:

2971 3958 5928
72 72 71
73 71
73 71
72
73

The order these appear in is not important, but if the value appears multiple times, the truck number must be returned multiple times.

I have tried every lookup, match, and index formula I can think of and I can't seem to find anything that will search multiple columns to do what I need or, if it does, it wont return the value multiple times.

I'm open to a VBA option, but for the most part, my experience there is limited to copy/paste & clear functions. Thanks in advance for any help!


Solution

  • Using Dynamic array formula:

    =LET(
        rng,B2:D4,
        trk,A2:A4,
        title,SORT(UNIQUE(TOROW(rng),TRUE),,,TRUE),
        ptbl,DROP(REDUCE("",title,LAMBDA(z,y,HSTACK(z,TOCOL(IF(rng=y,trk,NA()),3)))),,1),
        tbl,VSTACK(title,ptbl),
        IF(ISERROR(tbl),"",tbl))
    

    enter image description here