LOOKUP with Criteria returning multiple values with TEXT JOIN

Excel Gurus,

Given that I have a dynamic array result output that I'd like to do a lookup of (cell I3:I7) against a table, I can do it with XLOOKUP. However, I have another condition that IF the XLOOKUP return an error, I'd like to then do a LOOKUP based on the cells to the left of it (cell Ex:Hx).

The problem here is that, my cells of the error are not dynamic array, it was a manual user input (cell Ex:Hx).

I have a PARTIAL INDIVIDUAL working formula, but do not know:

1. Making my TEXTJOIN with UNIQUE and FILTER formula to return #N/A for PO id that's not found
2. Combining both XLOOKUP and TEXTJOIN with UNIQUE and FILTER formula into 1 and have it spill dynamically based on the data size.

MY current formula:

Please Login or Register  to view this content.

Lookup with Criteria Dynamic Array.png

Perhaps in J3:

Please Login or Register  to view this content.

WBD

Hmmm. OK. Hopefully you have MAKEARRAY then:

Please Login or Register  to view this content.

WBD

It's only a one column array so c is always 1 and is ignored. Glad it's working for you.

WBD