Just to demonstrate a different mindset:-
The first step is to define a named formula 'matchedRecordNum'
= IF( Table1[xCODE]=lookupCode, Table1[recordNum] )
that returns a list of record numbers corresponding to the matched records. Using a named formula both reduces the nesting levels of the final formula and, at the same time, evades the implicit intersection that destroys normal worksheet formulas.
Returning to the worksheet, the list is compacted, as normal, using SMALL
= INDEX( Table1[location], SMALL( matchedRecordNum,Table1[@seq]) )
Since the output list is aligned with the sequence numbers CSE is no longer needed though, if you are like me and have forgotten that the Enter key works without Ctrl+Shift, CSE will work just fine provided one omits the '@' from [@seq].
Other features of the model are a rejection of the spreadsheet practice of reference by location with its A1 notation, which has the effect of reducing the traditional grid and headings to the role of 'sheet junk'.
For all that, I can't wait for the simplification dynamic arrays will bring to the construction of spreadsheet solutions.
Postscript. The structured reference CODE in square parenthesis made a spectacular mess of my first attempt to post this reply. Second try lucky with the 'x' placed to remove the keyword.
