#NO MATCH error

Hey everyone!

I seemed to have ran into an issue using a vlookup formula. When I first wrote it out it worked for that cell but when I dragged it down the whole column it no longer worked.

=IF(VLOOKUP([H145 A/C #]@row, {QG Schedule Tracker Range 4}, 2) = "Not in SS", "No", "Yes")

This checks if the aircraft number in that row matches one in the first column of another sheet. If it does, then it checks to see if the second column says "Not in SS". If it does then it returns a No and if its doesn't then it returns a yes.

I have tried using a VALUE function as a work around to make sure both of the aircraft number columns are reading the same. I also tried an IFERROR function. I also checked to see if the problem was with using a primary column on one of the sheets, but I made a new column and nothing changed. I also tried to manually rewrite out the formula instead of dragging it down but that also did not change anything.

Any help or advice would be greatly appreciated!


Rachael Lander

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/06/22 Answer ✓

    What about using an INDEX/MATCH inside an IF statement?

    =IF(INDEX({QG 1 column}, MATCH([H145 A/C #]@row, {Aircraft Column}, 0)) = "Not in SS", "No", "Yes")

    where {QG 1 Column} is referencing the second column in the other sheet, and {Aircraft Column} is referencing the Aircraft column in the other sheet (obviously :) )

    I tend to have better luck with INDEX/MATCH than with VLOOKUP in Smartsheet.


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!