Lookup formula - treat no-registered cells as blank
 

I try to improve my excel spreadsheet used in a football cup.
I have one sheet (RegisterResult) where all matches are sorted on match
number.
I have one sheet for every "division" with math results and a table.
I want to register all match results in the RegisterResult and have it
automated updated in the correct "division" sheet. In the sheet for
"division" I have a function which almost do the job.
=VLOOKUP(A5;RegisterResult!A2:L217;10;) where A5 refers to match no. and
column 10 is the result for the home team. I have similar function for the
away team.
When I register a result in the RegisterResult sheet, the lookup function
works OK in the "division" sheet. The problem is that it fills in 0 - 0 as
result in the "division" sheet for the rest of the matches and hence mark it
as a draw in the attached table.
I have tried to add ISBLANK in the lookup function above, but I does not get
it work.

Hoping to get a solution as the cup is this weekend.

It depends on how the rest of the formula looks like.

Op.1
=IF(ISERROR(1/(VLOOKUP(A5;RegisterResult!A2:L217;10));"";VLOOKUP (A5;RegisterResult!A2:L217;10))

Op.2
=IF(ERROR.TYPE(1/(VLOOKUP(A5;RegisterResult!A2:L217;10))=2;"";VLOOK UP(A5;RegisterResult!A2:L217;10))

...might do the job.

Ola Sandström


--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=378600

=IF(ISBLANK(VLOOKUP(A5;RegisterResult!A2:L217;10)) ;"";VLOOKUP(A5;RegisterResult!A2:L217;10))
=IF(ISNUMBER(VLOOKUP(A5;RegisterResult!A2:L217;10) );VLOOKUP(A5;RegisterResult!A2:L217;10;"")

works for me

Ola


--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=378600

Olasa.
Thanks for your input. However, I get an error message referring to the ;"";

Here is more info that might help.

This formula is placed in J5
I have been trying a little of the same as you suggest, but it also fails.
=IF(ISBLANK(VLOOKUP(A5;RegisterResult!A2:L217;10)) ;"
";VLOOKUP(A5;RegisterResult!A2:L217;10))

Here is the related formula (in T5) to the cell above
=IF(ISBLANK(J5);0;IF(J5-K50;3;IF(J5-K5=0;1;0)))
Returns the points either 3 (win), 1 (draw) or 0 (loose)

This formula only works if the cell J5 is empty or contains a number (goals).
The formula I have tried above " " results in a #Value! in cell T5.

So instead of " " as return value if ISBLANK is TRUE it should have been
something like "Leave the cell as it is"/empty.

gublues.


olasa skrev:


It depends on how the rest of the formula looks like.

Op.1
=IF(ISERROR(1/(VLOOKUP(A5;RegisterResult!A2:L217;10));"";VLOOKUP (A5;RegisterResult!A2:L217;10))

Op.2
=IF(ERROR.TYPE(1/(VLOOKUP(A5;RegisterResult!A2:L217;10))=2;"";VLOOK UP(A5;RegisterResult!A2:L217;10))

...might do the job.

Ola Sandström


--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=378600

Hi again,
Yes, it ended up working here as well. (I used your alt. 1 formula in cell J5)
The cell with the formula below contains no visible characters.

The remaining issue is to get the next formula to work.

When using your alt. 1 formula in J5
The next cell T5 with the formula
=IF(ISBLANK(J5);0;IF(J5-K50;3;IF(J5-K5=0;1;0)))
reports an error #Value! (because the K5 is not blank?)

If this works in your sheet, I think I need a break!!!

gublues


olasa skrev:


=IF(ISBLANK(VLOOKUP(A5;RegisterResult!A2:L217;10)) ;"";VLOOKUP(A5;RegisterResult!A2:L217;10))
=IF(ISNUMBER(VLOOKUP(A5;RegisterResult!A2:L217;10) );VLOOKUP(A5;RegisterResult!A2:L217;10;"")

works for me

Ola


--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=378600