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!