Search code examples
excelexcel-formulaexcel-match

Using IF, INDEX and MATCH to retrive the value out of the two column that is not blank

I want to use IF, INDEX and MATCH function together to get the output from the another sheet that has two columns (one of them in always blank and so need value from a column which in not blank).

The formula I'm using looks like :

=IF(ISBLANK('DATA 1'!B:B);
    INDEX('DATA 1'!B:B;MATCH(OUTPUT!B14;'DATA 1'!A:A;0));
    INDEX('DATA 1'!C:C;MATCH(OUTPUT!B14;'DATA 1'!A:A;0)) )

This formula is returning values from one column only and when the corresponding column is blank it shows #N/A .

I want it to show the value from whichever column (out of the two on another sheet) that is not blank.

Solution

  • You should be using:

    =IF(ISBLANK(INDEX('DATA 1'!C:C,MATCH(OUTPUT!B27,'DATA 1'!A:A,0))),INDEX('DATA 1'!B:B,MATCH(OUTPUT!B27,'DATA 1'!A:A,0)),INDEX('DATA 1'!C:C,MATCH(OUTPUT!B27,'DATA 1'!A:A,0)))

    though you need to be aware that ISBLANK will return FALSE when passed a null string (""), so if any of your entries in 'DATA 1'!B:B or 'DATA 1'!C:C contain such an entry (perhaps as a result of formulas in those cells), then the above will not give correct results.

    As such, more rigorous is:

    =IF(INDEX('DATA 1'!C:C,MATCH(OUTPUT!B27,'DATA 1'!A:A,0))="",INDEX('DATA 1'!B:B,MATCH(OUTPUT!B27,'DATA 1'!A:A,0)),INDEX('DATA 1'!C:C,MATCH(OUTPUT!B27,'DATA 1'!A:A,0)))

    Assuming the returns are text, not numeric, you could also use the shorter:

    =LOOKUP(REPT("z",255),INDEX('DATA 1'!B:C,MATCH(OUTPUT!B27,'DATA 1'!A:A,0),N(IF(1,{1,2}))))

    though its brevity is arguably offset by its complexity and, again, this will fail if null strings are present in those ranges.