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.
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:
ISBLANK
0 leading zeroes (but also impact cells containing zero as solutions below)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.
Edit to add an explanation.
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…”
Please note:
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.)
Please also note:
The correct answer to the why-question was given by @PYS.
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?”.
@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.)