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 #.
Best 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


My Index/Match on Target worksheet is not recognizing (matching) PBY Store #

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.

@Mr. Chris THANK YOU!
I noticed the indents differences, I suspected the issue was not recognizing the value as a number I just didn't know how to fix.
Awesome. I will definitely bookmark this for future reference (I'm not likely to make that mistake again though). :)

Excellent! Happy this worked for you :)

The left/right alignment for texts vs numbers is only true in columns other than the Primary Column. The Primary Column will always be left justified. Another thing to keep in mind is that text based functions such as LEFT, RIGHT, MID, and JOIN will ALWAYS output a text value on their own unless paired with a VALUE function for conversion.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!