Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
=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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pasting or moving formula cells without updating formulas | Excel Discussion (Misc queries) | |||
How To Use Cells Without Values in a Formula | Excel Worksheet Functions | |||
How do i get a formula for word recognition in cells | Excel Discussion (Misc queries) | |||
Conditional formatting on cells with a VLOOKUP formula in them | Excel Discussion (Misc queries) | |||
My formula screws up other reference cells... | Excel Discussion (Misc queries) |