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

Question

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.

I think you need a separator (`;` or `,`) between the two INDEX statements — Mathias R. Jessen, Oct 21 '15 at 12:34
I have it; I just missed it while pasting here... = 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))) — Mukund Jha, Oct 21 '15 at 12:40
Can you post an example of what the columns look like? Do you know that all values you are trying to look up are present? — Robin Gertenbach, Oct 21 '15 at 12:43
Hey ya thanks..! It is answered in the comment box. Thank you very much :) — Mukund Jha, Oct 21 '15 at 13:01

score 0 · Accepted Answer · edited Dec 02 '15 at 22:33

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.

You're welcome. Feel free to mark it as the answer, in that case. — XOR LX, Oct 21 '15 at 13:02

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

1 Answers1