Search code examples
excelexcel-formulapriority-queue

Excel | Priority-Queue/Combining ISNA() and Cell=0 in OR-Function

I created this nested Excel formula to display the first available value (<>0) in line with my prioritisation (see below) of sources. The first value that is available and <>0 shall be returned to cell Y6.

Prio: 1. Z6 2. AA6 3. AB6 4. AC6

Formula: =IFS(AND(ISNA(Z6)=FALSE;Z6<>0);Z6;AND(OR(ISNA(Z6);Z6=0);AND(ISNA(AA6)=FALSE;AA6<>0));AA6;AND(OR(ISNA(Z6);Z6=0);OR(ISNA(AA6);AA6=0));AB6)

As you can see AC6 is currently not included, as an error occurred before I got to this part. It seems that

OR(ISNA(Z6);Z6=0)

does lead to an #N/A-error.

Hence, my question, how can I combine ISNA(Z6) and Z6=0 within an OR-Function? In general, if someone has a more elegant solution instead of my approach with the IFS-Function highly appreciated too. :)

Regarding the possible values in the respective cells:

  1. Z6 OR(#N/A;0;value<>0)
  2. AA6 OR(#N/A;0;value<>0)
  3. AB6 OR(Blank;0;value<>0)
  4. AC6 OR(Blank;0;value<>0)
Solution

  • You can use an Array Formula in Y6 as follows:

    =index(Z6:AC6,match(true,1/Z6:AC6>0,0))

    and press Ctrl-Shift-Enter