3. Lookup and reference formulas for CAE result mapping

In CAE post-processing, the hardest part is rarely the solve itself — it is reconciling identifiers across artifacts that were never designed to agree. A solver writes nodal displacements keyed by NID; a fatigue tool exports hot-spot stresses keyed by element EID; a load-case matrix in a spreadsheet keys results by LC name and a separate sheet keys them by numeric SUBCASE. Before any contour plot, margin table, or correlation chart can be trusted, these keys must be joined deterministically. Excel's lookup family — XLOOKUP, INDEX/MATCH, and OFFSET — is the practical toolkit for that join, provided you understand what each one guarantees about order, direction, and volatility.

Choosing the right lookup primitive

The three functions differ in a way that matters for engineering data: XLOOKUP searches a lookup array and returns the corresponding item from a return array, with native support for exact match, approximate match, reverse search, and a not-found fallback. INDEX/MATCH separates the position-finding step (MATCH) from the value-retrieval step (INDEX), which makes it the right choice when you need the row position itself — for example, to feed a second lookup or to slice a block of load cases. OFFSET returns a reference displaced from an anchor by a row and column count; it is the only one of the three that is volatile and reference-returning, which makes it powerful for dynamic ranges but expensive in large workbooks.

A useful rule of thumb for CAE result mapping: use XLOOKUP for one-to-one key joins (node ID → displacement), use INDEX/MATCH when the match position is reused (element ID → row index → multiple result columns), and reserve OFFSET for genuinely dynamic windows such as a sliding set of time steps or a growing result block.

Joining node IDs to nodal results with XLOOKUP

Assume a sheet NODES with NID in A2:A5001 and displacement components UX, UY, UZ in B:D. A correlation sheet lists the node IDs of interest in A2:A20. The exact-match join is:

=XLOOKUP($A2, NODES!$A$2:$A$5001, NODES!$B$2:$B$5001, "MISSING")

Three details make this robust in practice. First, the fourth argument is a not-found fallback: a node present in the correlation list but absent from the solver output returns the literal "MISSING" instead of #N/A, which is far easier to filter and audit. Second, the lookup array and return array must have identical row counts — a common failure is selecting A2:A5001 against B2:B5000, which returns #VALUE!. Third, if the solver exports IDs as text (a frequent artifact of CSV round-trips), XLOOKUP will not match numeric IDs; normalize with VALUE() or TEXT() on both sides before joining.

For a multi-column return, pass a two-dimensional return array and let the result spill:

=XLOOKUP($A2, NODES!$A$2:$A$5001, NODES!$B$2:$D$5001, "MISSING")

This returns UX, UY, and UZ in adjacent cells. If you need a specific component only, index into the spill with INDEX(..., 1, 2) for UY, or simply point the return array at column C.

Element IDs and multi-column results with INDEX/MATCH

Element results often carry several stress measures per element — von Mises SVM, equivalent plastic strain SEQV, and a utilization ratio. When the same element ID must pull several columns, computing the row position once and reusing it is cleaner and faster than three separate XLOOKUP calls:

=MATCH($B2, ELEMS!$A$2:$A$8001, 0)
=INDEX(ELEMS!$B$2:$B$8001, $C2)
=INDEX(ELEMS!$C$2:$C$8001, $C2)

Here C2 holds the MATCH result. The 0 match type is exact — never use 1 or -1 for ID lookups, because approximate match assumes sorted data and will silently return the wrong element when IDs are non-contiguous (which they almost always are after mesh refinement). A useful diagnostic: if MATCH returns #N/A but the ID visibly exists, check for leading/trailing spaces with TRIM() and for numeric-vs-text storage with ISNUMBER().

The INDEX/MATCH pattern also supports a two-way lookup, which is exactly what a load-case matrix needs: match the element ID down the rows and the load-case name across the columns, then retrieve the intersection.

=INDEX(ELEMS!$B$2:$Z$8001,
       MATCH($B2, ELEMS!$A$2:$A$8001, 0),
       MATCH($C2, ELEMS!$B$1:$Z$1, 0))

Dynamic result windows with OFFSET

OFFSET is the right tool when the result block itself changes size — for example, a transient run that appends time steps, or a load-case matrix whose row count grows as subcases are added. Anchored at LC!$A$2, a window covering the first three load cases and their factor column is:

=OFFSET(LC!$A$2, 0, 0, 3, 3)

Wrap it in COUNTA to make the height self-adjusting:

=OFFSET(LC!$A$2, 0, 0, COUNTA(LC!$A:$A)-1, 3)

Two cautions. OFFSET is volatile: every recalculation re-evaluates it, so a workbook with thousands of OFFSET calls will feel sluggish. And because it returns a reference rather than a value, it composes poorly with array formulas in older Excel builds. For static result blocks, prefer INDEX with explicit bounds; reserve OFFSET for the genuinely dynamic cases.

Load-case correlation and validation

Once node and element results are joined, the load-case dimension must be reconciled. A typical correlation table has columns NID, EID, LC, UX, SVM, and a computed margin. The LC column is joined to the load-case sheet with XLOOKUP to pull the subcase number and factor:

=XLOOKUP($C2, LC!$A$2:$A$200, LC!$B$2:$B$200, "NO_LC")
=XLOOKUP($C2, LC!$A$2:$A$200, LC!$C$2:$C$200, 0)

Validation is where lookup formulas earn their keep. Three checks catch most mapping errors before they reach a report. First, a completeness check: =COUNTIF(MAP!$D$2:$D$500, "MISSING") should be zero; any nonzero count means IDs in the correlation list have no counterpart in the solver output. Second, a duplicate check on the source key: =SUMPRODUCT(--(COUNTIF(NODES!$A$2:$A$5001, NODES!$A$2:$A$5001)>1)) flags repeated node IDs, which cause XLOOKUP to return only the first match silently. Third, a type check: =SUMPRODUCT(--ISNUMBER(NODES!$A$2:$A$5001)) should equal the row count; a shortfall indicates text-stored IDs that will fail numeric joins.

The practical outcome of this section is a correlation table that is auditable: every joined value can be traced to a specific source row, every missing key is flagged rather than silently blank, and every duplicate or type mismatch is surfaced by a validation count. That table is the foundation for the contour plots, margin summaries, and sensitivity studies that follow.