INDEX returns '0' when destination cell is empty - can this be changed?

[SteveSGSJ]

I am not an experienced excel person... trying to make a spreadsheet for a rural pool league. Using (cutting, pasting & modifying) from some other spreadsheets.
The following INDEX statement returns a 0 when the destination cell is empty... can I change the statement(add a parameter) to have it return an empty cell instead of the 0??

=INDEX('TEAM REGISTRATION'!$A$6:$Z$26,MATCH($A$15,'TEAM REGISTRATION'!$A$6:$A$26,),MATCH(C4,'TEAM REGISTRATION'!$A$6:$Z$6,))

Granted there may be completely different ways to accomplish what I am doing... But I have got my head around this statement, starting with a new one would be hard for me... not experienced in EXCEL

Any help in fixing this statement would be much appreciated.

Steve

 

[sandy666]

check how to use these two fuction:
INDEX()
MATCH()

 

[Aladin Akyurek]

What does your INDEX return if the result cell is not empty?

 

[mikerickson]

Typically, =A1 will return 0 if A1 is empty, so this behavior is expected.

One way around that is to use =A1&"".

Try changing your formula to
=INDEX('TEAM REGISTRATION'!$A$6:$Z$26,MATCH($A$15,'TEAM REGISTRATION'!$A$6:$A$26,),MATCH(C4,'TEAM REGISTRATION'!$A$6:$Z$6,)) & ""

 

[SteveSGSJ]

I looked at these both again. My understanding is that the MATCH functions are gathering the parameters for the INDEX function ( the what to look for in the column then the row ). With that said, am I to assume that the index function cannot be modified to avoid returning the zero that exists in the intersecting (destination) cell?

 

[SteveSGSJ]

The value within that cell. All cells being searched have a numeric content. I am trying to avoid bringing over the zero values when they are hit.

 

[SteveSGSJ]

This gives me a formula error. Was the intention to add ' &"" ' to the end of the statement? You mentioned to use ' =A1&"" '.

 

[SteveSGSJ]

Sorry..that was incorrect... all cells are numeric, but some are empty. Would like hte return to be empty, or blank, as well.

 

[sturnusek]

You can always use Format Cells function to get rid of this issue. Select the cells where the issue occurs and then simply press CTRL+1, Number > Custom and in the type box, type: 0;;;@

 

[jtakw]

Hi,

See if this solves your problem:

=IFERROR(1/(1/INDEX('TEAM REGISTRATION'!$A$6:$Z$26,MATCH($A$15,'TEAM REGISTRATION'!$A$6:$A$26,),MATCH(C4,'TEAM REGISTRATION'!$A$6:$Z$6,))),"")