Lookup formula - treat no-registered cells as blank

[Guest]

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.

 

[olasa]

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;"";VLOOKUP(A5;RegisterResult!A2:L217;10))

...might do the job.

Ola Sandströ

 

[olasa]

=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

Ol

 

[Guest]

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-K5>0;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:

 

[Guest]

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-K5>0;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: