Forum Discussion

JaromV's avatar
JaromV
Copper Contributor
Jun 23, 2022

Can VLOOKUP return multiple cells found in a single row?

I made a dashboard tab where I have a an input cell that contains a list of cells from the data validation tool. This is my source of lookup value. It looks like this:       My table array ...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jul 25, 2022

JaromV  Following up mathetes post.

What you see is of the form of the first table but what Excel sees is the second

What you actually want is more like

To do this, I used a couple of Lambda functions

 

FillTableλ = LAMBDA(tbl,
    LET(
        fInv,  FillDownλ(CHOOSECOLS(tbl, 1)),
        fSKU,  FillDownλ(CHOOSECOLS(tbl, 2)),
        fPort, FillDownλ(CHOOSECOLS(tbl, 3)),
        HSTACK(fInv, fSKU, fPort, Container)
    )
);

FillDownλ = LAMBDA(item,
    SCAN("Hdr", item, LAMBDA(last, curr, IF(curr <> "", curr, last)))
);

 

At this point, you may wonder whether the result is worth the effort?  The next step is simpler though.

The worksheet formula

 

= LET(
      filledTable, FillTableλ(table),
      inv,         TAKE(filledTable,,1),
      filtered,    FILTER(filledTable, inv=selectedInv),
      DROP(filtered,,1)
  )

 

gives

so it is possible to make progress.

Note: it would be more efficient to apply the filter earlier in the process but that adds complexity because the port name would not necessarily lie within the filtered dataset.

 

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jul 25, 2022

The file demonstrates some of the ideas but, at the current time, is only accessible to Excel 365 Insider beta users.

The worksheet formula probably could do with some tidying up

= LET(
      filledTable, FillTableλ(table),
      invoice,     TAKE(filledTable,,1),
      filtered,    FILTER(filledTable, invoice=selectedInv),
      data,        DROP(filtered,,1),
      SKUs,        TEXTJOIN(¶,,TAKE(data,,1)),
      port,        UNIQUE(CHOOSECOLS(data,2)),
      containers,  TEXTJOIN(¶,,CHOOSECOLS(data,3)),
      VSTACK(SKUs,port,containers)
  );

¶ = "," & CHAR(10);

Resources