#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
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!