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

Amy D

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

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!