Is there a solution for a v-lookup that is returning a NO MATCH for an auto number in another sheet?

This is an odd one. I have a source sheet Contract Tracker with an Auto ID column where I initially started the numbering sequence to include a suffix and then recently changed it only to be numerical. One a second sheet, I am entering the Row ID for select items from the source Contract Tracker and formulas are pulling over select data for a summary.

Since the change to the numbering sequence of the Row ID, any Row IDs without the suffix are returning a "#NO MATCH" error.

Formula: =VLOOKUP([Row ID]@row, {Contract Tracker Range 2}, 4, false)

Row ID column is the same on the Contract Tracker. When the old numbers are referenced like "11225-S" the formula returns values, when the new sequencing is entered "11300" the error occurs.

Any ideas on a solve?

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Insert a new text/number column on the source sheet (can be hidden after setting up). Then use

=[Row ID]@row + ""

And then reference this helper column in your VLOOKUP.

All comments

Paul Newcome

Insert a new text/number column on the source sheet (can be hidden after setting up). Then use

=[Row ID]@row + ""

And then reference this helper column in your VLOOKUP.

LSW

Paul - This is interesting. The fix did work, but now I cannot convert the column to formula without getting a "This column formula syntax isn't quite right" message.

Paul Newcome

You should be able to convert that to a column formula. I use it very regularly. Are you actually using "@row", or did you specify a row number? Using "@row" exactly as I have it in my suggestion should do the trick.

LSW

Okay, so I had to add the helper column with the Row ID formula to both sheets and reference for it to work. Really odd that this is the only way to work around

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.