#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

Find more posts tagged with

Sheets

Formulas

Cell linking

Accepted answers

Jeff Reisman

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.

All comments

Paul Newcome

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.

Rachael Lander

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

Jeff Reisman

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.

Paul Newcome

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).

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.