Match formula not returning value in Numbers

It will be nearly impossible for us to figure this out for you. I suggest you do some sleuthing of your own.

There are three cases in your formula where the result will be "". I suggest you temporarily change them to "1", "2", and "3" so you can tell which one is being invoked. That might help narrow down where the issue is.

You can test out smaller pieces of your formula to see if the results are what you expect. Save the text of your formula somewhere else, like maybe in TextEdit, then copy/paste smaller pieces back to the cell. For instance, you can try this piece

MATCH('Controller 5 - Cumulative Option Price / Profit & Loss with Cumulative Greeks'::$U$6, A$1:KE$1,0)

If it does not give a correct result then copy/paste something even smaller from it such as

'Controller 5 - Cumulative Option Price / Profit & Loss with Cumulative Greeks'::$U$6

You can manually search A1:KE1 for what you think is a match for U6 and figure out what is different about them, why they don't match.

And so on with other pieces of the formula until you find which piece of the formula is wrong.

It's really hard to triage/troubleshoot this kind of thing through screenshots, since some of the formulas require tribal knowledge (i.e. you know what you're trying to achieve, but others may not), and the cross-table linking is hard to follow since the table names aren't included in the screenshots, so we have no way (or at least a hard time) determining what you're trying to reference.

Additionally, your formula includes references that aren't clear... for example:

> ... OFFSET($A$2, ROW(reference)...

where there's no clear indication as to what reference is.

That said, I'd probably take a slightly different approach to simplify things.

Judging by the color coding, it seems like you're doing the same thing over and over - for example, you're using MATCH() to find a value, which you're plugging into OFFSET() to find a reference. You're doing this twice, once to see if it's ISBLANK() and once to see if it's ISERROR(). You could simplify this by using LET() to do this once and then simply comparing the value (which will speed up the spreadsheet, too, since fewer calculations need to be done).

LET(matchkey,'Controller 5 - Cumulative Option Price / Profit & Loss with Cumulative Greeks'::$U$6,

    lookuprange,A$1:KE$1,

    lookupvalue,MATCH(matchkey,lookuprange,0),

    targetcell,OFFSET($A$2,ROW(reference)−ROW($LN$2),lookupvalue −1, 1, 1),

    IF(

        OR(
matchkey="", ISBLANK(targetcell), ISERROR(targetcell)),

        "",

        targetcell)

    )

)

The idea here is to use LET() to assign various calculations to variables, and use those variables rather than the full calculation in the functions. This makes it easier to read and debug.

In this case, I create variables for matchkey and lookuprange, which are used in a MATCH() statement. The result of this MATCH() is assigned to a new variable called lookupvalue which, in turn, is passed into OFFSET() to find the reference you're looking for, which is stored in targetcell.

From here, I then check if the matchkey is empty, if the target cell is blank, or if the lookup generates an error. For any of these I return a blank string, otherwise I return the targetcell value.

Using this form it should be easier to triage. You can change the OR() statement to return the specific cell or error that's triggering, or add additional checks before working out what value to return. For example, simply replacing the 'IF(...)' with simply matchkey will show you what the MATCH is returning.

(note that I may have transposed some of the references when re-typing the formula, so please check that they're correct before relying on this :)