I feel like a dummy. I basically need a column that says - If column a2=0 then index and match column C, if that returns an error index and match column e, else index and match column a

Current formula that only works for If column a2=0 then index and match column C, else index and match column a.

=IF(A2=0,"",IFERROR(INDEX('Planned Look UP'!C:C,MATCH(C2,'Planned Look UP'!C:C,0)),IFERROR(INDEX('Planned Look UP'!A:A,MATCH(A2,'Planned Look UP'!A:A,0)),"n/a")))

I can't for the life of me get the column E step in there.

  • Your outside IF statement currently returns nothing (the empty string "") when A2=0 and runs the IFERROR(INDEX(MATCH))) for Column C when A2 is NOT 0. Simply put the Column C check where your "" are. Then change your Column A check to Column E (in the same location). The structure you want is: IF(A2=0, IFERROR(Column C,IFERROR(Column E)),IFERROR(Column A)) – Excel Tactics Apr 1 '14 at 2:44

IF statements have two branches, IFERROR statements only have 1.

Your first condition 'IF(A2=0...' does nothing when the condition is true ',""' and does the error check if the condition is false ', IFERROR(...' which is the opposite to your spec. Change the start of this to 'IF(A2<>0,'.

To insert the E column condition, you need to place the condition at the branch of the C column condition where it exits if there is an error. Following the above, the formula you are looking for should be:

=IF(A2<>0, "", IFERROR(INDEX('Planned Look UP'!C:C, MATCH(C2, 'Planned Look UP'!C:C, 0)), IFERROR(INDEX('Planned Look UP'!E:E,MATCH(E2,'Planned Look UP'!E:E, 0)), IFERROR(INDEX('Planned Look UP'!A:A,MATCH(A2,'Planned Look UP'!A:A, 0)), "n/a")))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.