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

PeggyLang · 02/02/24

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

=Chris Palmer · 02/05/24

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.

heyjay · 02/02/24

CAn you please provide more information like you formula.

PeggyLang · 02/02/24

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

=Chris Palmer · 02/05/24

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.

PeggyLang · 02/06/24

@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). :)

=Chris Palmer · 02/06/24

https://community.smartsheet.com/discussion/comment/418109#Comment_418109

Excellent! Happy this worked for you :-)

Paul Newcome · 02/06/24

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.

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

Best Answer

Answers

Help Article Resources

Categories