Can VLOOKUP return multiple cells found in a single row?

[JaromV]

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 looks something like this:

I wish to input the Commercial Invoice No. to the input cell mentioned above and the result would display the corresponding information for all columns in the dashboard. However most columns have multiple cells that are included in my search. The "SKU" Column for example has multiple cells that needs to be shown. I know merging can be problematic, but I do not know how else to organize the data table. Tips would be highly appreciated! TIA!

[#2]

@JaromV 

You say 

I know merging can be problematic, but I do not know how else to organize the data table.

And with that acknowledgment you've put your finger on the problem. A table, an Excel table, does NOT work with merged rows for such things as Invoice numbers or LOT, etc. It may "look nice" to the human eye, but it totally interferes with the computer's abilities--which are many--to parse the data as you now want to do.

How else to organize? Repeat the Invoice number and Sales contract...whatever needs to be done. Basically, you need to reorganize your data into a standard Excel Table. Then you'll probably find that FILTER (a new relatively new function that does require the most current version of Excel) will work wonders for you. But start with  reorganizing your data.

Herewith, some references for you to research.

On Tables: https://exceljet.net/glossary/excel-table

On FILTER: https://www.youtube.com/watch?v=9I9DtFOVPIg

[#3]

@mathetes I appreciate your comment a lot! Yes I do think I have to fix my data first and get rid of all the merged cells. I will look into the links you've posted. Once again thank you!

[#4]

@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.

[#5]

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);