Converting a Vlookup to Index/Match within an IF and IFERROR function

I'm currently using this formula

=IFERROR(IF(DSP@row = "", VLOOKUP(Code@row, {ClientNo}, 7, false), VLOOKUP(DSP@row, {ClientNo}, 5, false)), "")

and it works great. However, I need to delete some columns on my reference sheet that are within the vlookup table. I want to convert the above formula to INDEX MATCH so I don't have to worry about this anymore but I'm just get a blank cell as a result. This is what I'm trying:

=IFERROR(IF(DSP@row = "", INDEX({Client#}, MATCH(Code@row, {Code}, 0)), INDEX(MATCH(DSP@row, {DSP}, 0))), "")

What am I missing?

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi @Jennifer Lenander ,

    You're missing the Index range in the 2nd half of your formula.

    =IFERROR(IF(DSP@row = "", INDEX({Client#}, MATCH(Code@row, {Code}, 0)), INDEX({MISSING RANGE}, MATCH(DSP@row, {DSP}, 0))), "")


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!