#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!
Thanks,
Rachael Lander
Best 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.
Regards,
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!
Answers

Double check the the data in the refernece column of the first screenshot is in fact being stored as a number.
You can insert a flag type column and use this column formula
=IF(ISTEXT(Aircraft@row), 1)
This should flag any row that is being stored as a text value.

I tried that on both sheets and neither had any flagged rows.

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.
Regards,
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!

The VLOOKUP should have worked. The only thing I noticed after taking a second look is that you are not leveraging the 4th position within the VLOOKUP to denote an exact match vs a close match.
If the INDEX/MATCH above worked where the VLOOKUP did not, then it is most likely here as the INDEX/MATCH above does indicate an exact match (the zero at the end of the MATCH function).
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 325 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!