If you need to search for a textual numeric in a cross-reference formula, you may need to use the CONTAINS function when the host source (ie, ReferenceSheetPickMatchColumn) uses an apostrophe to treat the number as a text value, like this:

=SUMIFS(ReferenceSheetPickContentColumn, ReferenceSheetPickMatchColumn, CONTAINS([CurrentSheetMatchColumn]@row, @cell))

This probably applies to all cross-reference cases, though I've tested only SUMIFS at this point.

Background: I had a column in the reference sheet containing textual numbers (eg, ‘44021 and ‘00000). SUMIFS was not able to find matches due to the leading apostrophe until a combination of the CONTAINS and @CELL functions was added to the formula. This solution was found for me by Genaro on the Enterprise Support team after he and I had spent nearly an hour trying all sorts of other ways to yield the expected result—and that was after I'd spent over an hour before calling him to try everything I could think of (eg, using a formula to force an apostrophe in front of the searched number; adding an apostrophe to the searched number on the formula sheet; creating a separate column in the reference sheet to strip off the apostrophe [using the VALUE function—but for account coding purposes I needed a five-zero account segment, so I could not just convert the column to strictly numerics], and so on).