V Lookup-how to change "#No Match" to display different result

asimcock ✭✭
edited 12/09/19 in Formulas and Functions


I"m trying to get the VLookup to not display "#No Match" when the search value is not found on the reference sheet.

 In this case I have the columns as checkboxes, I'd like the instead the blank checkbox or a different error phrase such as "Incomplete" would be fine as well. My last display option would be to leave it blank but I'd prefer one of the other two.

This is my current formula that returns the proper data if the box is checked.  

=VLOOKUP([Reference Column]3, {Agent/Manager Hand-off Tool Range 1}, 19, 0)

This was my attempt adding an iferror to the vlookup formula based on several postings. 

=IFERROR(VLOOKUP({Agent/Manager Hand-off Tool-Agt completed}, 13, [Reference Column]3, {Agent/Manager Hand-off Tool-Agt completed}, "Incomplete"))

This one gives me an"#Incorrect argument set"

Does anyone have any advice on how to have this display either option?  My first pick would be to display the empty or completed checkbox.  

Also less than ideal but better than current result, I've also tried to get it to display as blank by adding the 0 as the match type as well as the "" as part of the VLookup formula to display a blank, but that didn't work.  


Thank you in advance!





Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!