We are doing a match(index) formula and it doesn't work when there is a number with a leading zero

This is the current formula and it works for every other type, normal numerical numbers and alpha numeric numbers. When it is just numeric with a leading zero it will not return the data.

Does anyone know why this isn't working and what we can do to make it work?

=IFERROR(INDEX({Shipping Log - Date}, MATCH(IFERROR(VALUE([ld_lot]@row), [ld_lot]@row), {Shipping Log - Lot Number}, 0), 0), "No Shipping Log Entry")

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

My suggestion would be a helper column on both sheets that converts everything into text values.

=[Column Name]@row + ""

(plus quote quote)

Then referencing this helper column instead.

Amy D

Thanks Paul, I gave that a try and it didn't work.

Paul Newcome

Are you able to provide screenshots for context (with the suggested updates in place?

Amy D

I actually played around a bit more with the original formula and removed the iferror(value part of the formula and it fixed it....thanks for taking a look for me!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.