Index/Match Mismatch

Gary_ · 02/04/22

I must be making a silly mistake or be completely wrong - any help is appreciated. I'm fairly new at this, but learning.

I have a sheet that is largely based on submissions from a form, with additional columns to pull in information from other sheets. I have similarly used Index/match on other sheets, I believe in the same way, but it worked in those examples.

In this case, I have 4 columns that I am trying to pull in names from another sheet, based on matching the store #. When I get the results from my formula (below), it seems that I get the correct information for stores with a 3-digit number, but not ones that have a 4-digit number. Based on different actions I tried, I would either get the results of the first row or an #No Match.

The reference sheet that has the store list, which I will copy/replace periodically into this sheet to keep up-to-date, pastes the store # as '0001. I am not sure if that has something to do with my error or not. Also, the main sheet's column with store numbers is a dropdown that ideally I want to be restricted to particular options (for the form). Those numbers seem to be in text format as well, as I was fiddling with validating the value types to see if that had anything to do with my problem. Once I figure this out, I would like to make sure that I can make it a formula column, so that is applied each time a form is submitted.

=INDEX({SIM - Raw Data Export - Region Name}, MATCH([Current Location #]@row, {SIM - Raw Data Export - Store Number - reformatted}), 0)

I have tried helper columns converting from text to value, using the "right" function to see if I needed work around the apostrophe before the number (if that even matters), sorting, moving columns, and anything else I have been able to think of.

Jeff Reisman · 02/04/22

@Gary_

You are barking up the right tree here. INDEX/MATCH will never work to match mismatched data types. 1 and '0001 are quite different from each other.

There are two ways you can go here: either evaluate all store numbers as number, or evaluate them all as text values. I would suggest going with numbers, because it's way easier to turn 0001 into 1 or 0036 into 36 than it is to turn 1 into 0001 or 36 into 0036.

You can't turn text into numbers by using the RIGHT function, because RIGHT is a function reserved for text values. Fortunately, we have the VALUE function, which turns text into numbers. To use it, simply wrap it around your store number value in your helper column:

=VALUE([Current Location #]@row)

As far as your dropdown list of store numbers goes, if it looks like this, whatever is in the list with leading zeroes gets recorded as text, anything without leading zeroes gets stored as a number.

So you'd fix this with a helper column as well, turning everything into a number using VALUE function.

Jeff Reisman · 02/04/22

@Gary_

You are barking up the right tree here. INDEX/MATCH will never work to match mismatched data types. 1 and '0001 are quite different from each other.

There are two ways you can go here: either evaluate all store numbers as number, or evaluate them all as text values. I would suggest going with numbers, because it's way easier to turn 0001 into 1 or 0036 into 36 than it is to turn 1 into 0001 or 36 into 0036.

You can't turn text into numbers by using the RIGHT function, because RIGHT is a function reserved for text values. Fortunately, we have the VALUE function, which turns text into numbers. To use it, simply wrap it around your store number value in your helper column:

=VALUE([Current Location #]@row)

As far as your dropdown list of store numbers goes, if it looks like this, whatever is in the list with leading zeroes gets recorded as text, anything without leading zeroes gets stored as a number.

So you'd fix this with a helper column as well, turning everything into a number using VALUE function.

Gary_ · 02/04/22

Wow. This worked! Thank you so much. This was my first time using this forum beyond reading all of the helpful posts, so I will need to rely on this more.

Thanks again - life saver! -g

Jeff Reisman · 02/04/22

https://community.smartsheet.com/discussion/comment/318703#Comment_318703

Happy to help, some of my best ideas come from what I figure out while helping others!

Index/Match Mismatch

Best Answer

Answers

Help Article Resources

Categories