 # Why returning Zero value

Please see attachment and info on document

I have spend few hours with different variations and not able to fix, not sure why its returning a zero value.
Just because the 1st column is zero or maybe thats not the reason.

thank you in advance for any help provided.

Values equal Zero does not return correctly 09-24-2016.ods

in the document TEAM 1 and T should return a value of 12 from Row 11, and the Zero value in Column M should have a value from Column B. For some reason Index - Match is only picking up value from column B if its the first name for each row If their is no value in the first name of both names then it will not return a value , but it should find the value in next row since match finds that value but does not display it correctly How is this fixed

Hi

Same result with `=B10`

You always get 0 when the referred cell is empty. Several ways to do this according to your needs:

• Adapt you formula using `ISBLANK`
• Use format `0 leading zeroes` (but also impact cells containing zero as solutions below)
• Do not print 0 (`Format``Page``Sheet` tab▸untick `Zero values`)
• `Tools``Options``Calc``View``Display: Zero values`

Adjust the formula seems the best solution in this case…

Regards

I’m not understanding what your referring to… in the document TEAM 1 and T should return a value of 12 from Row 11, and the Zero value in Column M should have a value from Column B.
For some reason Index - Match is only picking up value from column B if its the first name for each row
If their is no value in the first name of both names then it will not return a value , but it should find the value in next row since match finds that value but does not display it correctly
How is this fixed

Try the following formula for M10 and copy down the column.

``````=IFNA(INDEX(\$B\$10:\$B\$73,MATCH(K10&J10,IF(\$B\$10:\$B\$73<>"",\$C\$10:\$C\$73&\$D\$10:\$D\$73,""),0)),"")
``````

Only rows where the B column is not blank will be included in the search array.

Note that the MATCH function will only return the index of the first match found in the search array, it has no way to determine that you want to skip the first match because it doesn’t have a value in column B. The IF function added above will evalauate as an array result where the rows that B is blank will have a blank row in the array result.

“…where the B column is not blank will be included…”
`NOT(ISBLANK(B10))` and
`(B10<>"")`
yield different results if B10 is containing a formula which returned the empty string.
(The first formula returning TRUE then, the second one FALSE; numerically 1 vs. 0.)

`=IFNA(INDEX(\$B\$10:\$B\$73;MATCH(K10&J10&0;\$C\$10:\$C\$73&\$D\$10:\$D\$73&ISBLANK(\$B\$10:\$B\$73);0));"")`