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

 

[SteveSGSJ]

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

This statement returned a #NAME ? error

 

[SteveSGSJ]

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;;;@

THAT'S IT!!!! Thanks so much. Like I previously said, I am not experienced in EXCEL and would probably never found that resolution on my own. I have to add that to my "How To" folder.

I also tested it on the remainder of the cells that are returning a significant number, and all is well.

Thanks again,
Steve

 

[jtakw]

This statement returned a #NAME ? error

That most likely means your version of Excel pre-dates the IFERROR function, what version of Excel do you have?

 

[Aladin Akyurek]

This statement returned a #NAME ? error

That most likely means your version of Excel pre-dates the IFERROR function, what version of Excel do you have?

Not a good idea invoking 1/(1/… concept as it is not safe because not every division returns the divisor in the real world, that is, 1/(1/X) ≠ X.

 

[sturnusek]

THAT'S IT!!!! Thanks so much. Like I previously said, I am not experienced in EXCEL and would probably never found that resolution on my own. I have to add that to my "How To" folder.

I also tested it on the remainder of the cells that are returning a significant number, and all is well.

Thanks again,
Steve

Glad I could help. I was looking for exactly same thing few days ago and thought I’d share

 

[SteveSGSJ]

That most likely means your version of Excel pre-dates the IFERROR function, what version of Excel do you have?

Yes I do have an older version of EXCEL... like real old... 2000 ! I am one of those not really interested in 'keeping up' tech wise anymore... only replace what/when I NEED to.

Again... thanks for the help

 

[SteveSGSJ]

Glad I could help. I was looking for exactly same thing few days ago and thought I’d share

Nice to know there are others that believe in sharing the knowledge they have! IMO.. that's the way our world is suppose to work.

... and with that said, are there any mathematicians out there that would like a challenge?? My project is creating a spreadsheet to track statistics for a rural pool league. No interest (or money) to get into the online set-ups or join a 'sanctioned' league. I will try to parse out (in english) what I am trying to do and post it. Can it go in this thread, or should I start a new one. Hopefully my older version of EXCEL doesn't stop me from doing what needs to be done.
Thanks, Steve

 

[SteveSGSJ]

I think my formula issue is more suited in a different thread...