Formula returning 0% instead of correct value in the sheet...

Joyce McMurran

Not sure why my formula (using Index and Match) is returing 0% as a result of matching to another sheet? The correct data in the cell is = 55%. All other columns with same formula working correctly but one dept is showing an error o= 0%. Here is the formula =INDEX({Feb Score Column - BEAUTY}, MATCH(UII@row, {UII Store - Month Match Column - Beauty}, 0)).

Matching a store audit value to a grading sheet that has all stores included to use for reporting...

Thank you,

Joyce

Paul Newcome

Are you able to provide screenshots of your sheets with sensitive/confidential data removed, blocked, or replaced with "dummy data" as needed?

What is the data in the cell UII@row for the row in question? Is it possible that there are multiple rows on your source sheet that contain that data?

Genevieve P.

Hi Joyce,

The 0% means that it is finding a match... so it might have to do with your ranges. Is that a possibility?

Could you check the cross-sheet references to make sure it's referencing the correct columns (click in the middle of the range and select "edit cross sheet reference").

Keep in mind that if you edit a reference and change it to a different column, this will affect all formulas in your sheet with that reference name - all of those will now reference the new column. Is it possible that instead of adding a new reference for the March column you edited the February reference to be March instead? (Pulling in the 0, instead of 55?)

Paul Newcome

Are you able to provide screenshots of your sheets with sensitive/confidential data removed, blocked, or replaced with "dummy data" as needed?

What is the data in the cell UII@row for the row in question? Is it possible that there are multiple rows on your source sheet that contain that data?

Joyce McMurran

Hi Paul,

Here is destination sheet... the first has the formula and the 2nd one is the cell with 0% that has that formula in it...

Here is sheet audit with the score in the FEB column...

Joyce McMurran

Note: the No Match is fine as they did not do an audit for those that have that matching in the cell.

Joyce McMurran

Hi, checked the audit and it only has one entry for the store and it was 55%. Good catch!

Paul Newcome

Happy to help! 👍️

Joyce McMurran

I am still at a loss as to why I am getting 0% in the cell and the source sheet is 55%. After checking all the formulas it is correct . Not sure why? It is only a handful of cells that are not correct the rest on the grading sheet are correct... go figure?

Also, checked if there was duplicate data in other sheets or linking that might have caused it. Negative...

At this point the 10 cells I have used a cell link to the source sheet and that fixes the grade to 55% and can use for reports but still need to discover WHY it works for only some cells and not others...

Think I need to call in support and show my screen...

Thoughts?

Genevieve P.

Hi Joyce,

The 0% means that it is finding a match... so it might have to do with your ranges. Is that a possibility?

Could you check the cross-sheet references to make sure it's referencing the correct columns (click in the middle of the range and select "edit cross sheet reference").

Keep in mind that if you edit a reference and change it to a different column, this will affect all formulas in your sheet with that reference name - all of those will now reference the new column. Is it possible that instead of adding a new reference for the March column you edited the February reference to be March instead? (Pulling in the 0, instead of 55?)

Paul Newcome

@Joyce McMurran My apologies. When you said "Good catch" I took that to mean that was the problem and it was now working for you.

@Genevieve P has made a good suggestion. You are obviously getting a match which is why I asked about duplicate rows. Since it is not a case of duplicated rows, Genevieve's thought would be my next one as well. Double check your ranges to make sure they are in fact the intended selection(s).

Joyce McMurran

I will double check the references...thank you Genevieve and Paul. Am on the Smartsheet training webinar on formulas today so maybe that will help solve this. Will check back soon. Thank you... =)

Maury Hernandez

Hi!!! I'm having a similar issue!! why if I use a IF or SUMIF or formulas related is giving me back 0 (zero) when the criterion value number starts with 0?? trying to build something like this... Ahh and by the way, if the criterion value is NOT staring with 0 then the formula works perfectly. this is the formula:

=MAX(COLLECT({Service Report Range 1}, {Service Report Range 2}, $[VEHICLE ID (LAST 8 OF VIN)]1))

obviously i'm trying to collect the data from other table. all the ones starts in 0, returns 0, the other ones won't... and in the column named "VEHICLE" I'm using a VLOOKUP and works fine and it doesn't matter if the search value starts with 0 (zero), Example:

=IFERROR(JOIN(VLOOKUP($[VEHICLE ID (LAST 8 OF VIN)]1, {00 UNITS DATA BASE Range 1}, 3, false) + " " + VLOOKUP($[VEHICLE ID (LAST 8 OF VIN)]1, {00 UNITS DATA BASE Range 2}, 4, false) + " " + VLOOKUP($[VEHICLE ID (LAST 8 OF VIN)]1, {00 UNITS DATA BASE Range 3}, 5, false) + " " + VLOOKUP($[VEHICLE ID (LAST 8 OF VIN)]1, {00 UNITS DATA BASE Range 4}, 6, false)), "NOT FOUND")

Paul Newcome

@Maury Hernandez Are you able to provide screenshots of both sheets?

Maury Hernandez

@Paul Newcome

@Paul Newcome

that the sheet from where I want to collect the MAX date and and Mileage for this case, since this table is been used for tracking and obviously would have several duplicates, that's why I need to bring to my other sheet the most resent value, date/mileage, to here:

(some columns in the first screenshot sheet are hiding since it has several columns), but then again, my issue is only when the criterion value, in this case the "VIN#" starts with 0 (zero), the ones are not, then the formula works fine...

and VLOOKUP formula also work fine...

=MAX(COLLECT({Service Report Range 1}, {Service Report Range 2}, $[VEHICLE ID (LAST 8 OF VIN)]32))

Paul Newcome

It probably has to do with the leading zeros causing the data to be text values where the ones without the leading zeros are numerical values.

Try inserting a helper column into both sheets and use

=[Vehicle ID]@row + ""

to convert everything to text values.

Then you would use these two columns in your formulas.

Maury Hernandez

@Paul Newcome

I used in both sheets =VALUE([VEHICLE ID (LAST 8 OF VIN)]39) and create the formulas based on those two new columns... works perfectly!!!!

Thanks for the Help!

Paul Newcome

That works too. My version converts everything to text whereas your version converts everything to numbers.

Either way the main point of standardizing the data type is accomplished.

Happy to help. 👍️