Help with If / Match / Vlookup Formula

Options

Hello All,

I was away from Smartsheet for a bit but it's great to be back!

We have a lessons learned sheet that needs to pull information from either a sheet of open projects or a sheet of closed projects using a VLOOKUP. I'm using this formula:

=IF(MATCH([Project Name]@row, {Closed Projects Range 2}, 0) > 0, VLOOKUP([Project Name]@row, {Closed Projects Range 1}, 3, false), =VLOOKUP([Project Name]@row, {Project Portfolio Range 1}, 8, false))

to try to tell Smartsheet to use the VLOOKUP on the "Closed Projects" sheet if it finds a match for the project name there and to use the second sheet if it doesn't.

I'm getting good matches from the "Closed Projects" sheet but "#No Match" from the other. I suspect the problem is with the ">0" part of the MATCH argument of the IF formula, but I haven't been able to figure out the solution. I also tried a helper column using MATCH to find the matches on the open sheet and ISERROR for the first argument of the IF formula, but no luck there either.

Thank you in advance for any help.

Dave

Best Answer

  • Dave Hersher
    Answer ✓
    Options

    Figured it out using two helper columns. One MATCH and one IF(ISERROR). Probably not the most elegant solution, but it worked. Glad to share if it'll help anyone.

    If you read my original question and were thinking about it, thank you!

    Dave

Answers

  • Dave Hersher
    Answer ✓
    Options

    Figured it out using two helper columns. One MATCH and one IF(ISERROR). Probably not the most elegant solution, but it worked. Glad to share if it'll help anyone.

    If you read my original question and were thinking about it, thank you!

    Dave

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!