# Why returning Zero value [closed]

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.

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-09-12 16:49:31.612949

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

( 2016-09-25 20:52:05 +0200 )edit

Sort by » oldest newest most voted

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.

more

"...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.)

( 2016-09-26 11:48:35 +0200 )edit

The correct answer to the why-question was given by @pierre-yves samyn. This answer by @mark_t and my own answer to find in this thread ignore the "why" and actually assume a "how to get the expected result?".

( 2016-09-26 11:54:46 +0200 )edit

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 (FormatPageSheet tab▸untick Zero values)
• ToolsOptionsCalcViewDisplay: Zero values

Adjust the formula seems the best solution in this case...

Regards

more

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

( 2016-09-25 17:13:18 +0200 )edit

@JG101: The same idea as used to get combined matches from columns C and D regarding the content in the current row of columns K and J you may use to restrict the matches to rows where ISBLANK(Bnn) is false (numeric: 0). Enter
=IFNA(INDEX($B$10:$B$73;MATCH(K10&J10&0;$C$10:$C$73&$D$10:$D$73&ISBLANK($B$10:$B$73);0));"")
into M10 and fill down.

(Editing: See this reworked example.)

more