Index/Match not working when the Match column in source doc is a 'RIGHT' formula

I have a column in my source sheet where Store # is a RIGHT formula.

When I index match in my Target sheet I get 'NO MATCH' when trying to find Store #.

Tags:

Best Answer

  • =Chris Palmer
    =Chris Palmer ✭✭✭✭✭
    edited 02/05/24 Answer ✓

    Hi @PeggyLang

    Sometimes numbers in sheets are mistaken for being a text property rather than a number value. You can update your =RIGHT formula to the following: =VALUE(RIGHT([Site Number]@row, 4))

    You want to make sure every column/cell used in all formulas aligns with a number value. Your INDEX MATCH formula should also contain =VALUE as well so the data being referenced is consistently a number.

    You can spot numbers being read as text if they are indented to the left, like they are in your "Site Number" column is indented to the left by default. Where the "PBY Store #" is indented to the right which is a Number Value.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!