| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup Displays Blank
I can't figure out where have I gone wrong with this formula:
=IF(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130: $B$133,2,FALSE),"Ops Plant Engineer - "&GlobalOpsInput!$F$21) What I am trying to do is if the text is found in B25, then look at RefTable and pull in the data in the second column (which is a formula: ="Director Customer Service - "&RefTables!G121), if not found, then insert "Ops Plant Engineer" with the data in cell F21. If the data is found, the cell with the formula is blank, if it is not found then there is a #VALUE error. Even if I type something in the RefTable second column, the formula still returns a blank. Thanks for your help! jms |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup Displays Blank
=IF(ISNA(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A $130:$B$133,2,FALSE)),"Ops
Plant Engineer - "&GlobalOpsInput!$F$21,VLOOKUP(GlobalOpsInput!$B$2 5,RefTables!$A$130:$B$133,2,FALSE)) -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Jani" wrote in message ... I can't figure out where have I gone wrong with this formula: =IF(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130: $B$133,2,FALSE),"Ops Plant Engineer - "&GlobalOpsInput!$F$21) What I am trying to do is if the text is found in B25, then look at RefTable and pull in the data in the second column (which is a formula: ="Director Customer Service - "&RefTables!G121), if not found, then insert "Ops Plant Engineer" with the data in cell F21. If the data is found, the cell with the formula is blank, if it is not found then there is a #VALUE error. Even if I type something in the RefTable second column, the formula still returns a blank. Thanks for your help! jms |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup Displays Blank
The condition you've set in the test isn't whether a match is found, but
rather the result of the vlookup. Try =if(isna(vlookup(GlobalOpsInput!$B$25,RefTables!$A $130:$B$133,2,FALSE)),"Ops Plant Engineer - "&GlobalOpsInput!$F$21,vlookup(GlobalOpsInput!$B$2 5,RefTables!$A$130:$B$133,2,FALSE)) In words: if the vlookup fails (returns NA), set the result to "Ops Plant Engineer..."; otherwise use the result of the vlookup. "Jani" wrote: I can't figure out where have I gone wrong with this formula: =IF(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130: $B$133,2,FALSE),"Ops Plant Engineer - "&GlobalOpsInput!$F$21) What I am trying to do is if the text is found in B25, then look at RefTable and pull in the data in the second column (which is a formula: ="Director Customer Service - "&RefTables!G121), if not found, then insert "Ops Plant Engineer" with the data in cell F21. If the data is found, the cell with the formula is blank, if it is not found then there is a #VALUE error. Even if I type something in the RefTable second column, the formula still returns a blank. Thanks for your help! jms |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup Displays Blank
You two were sure quick and have solved most of the problem, but I am still
getting a #Value error when it doesn't find the condition when it should be entering the "Ops Plant Engineer -" with the value from GlobalOpsInput!$F$21. Any other thoughts??? "bpeltzer" wrote: The condition you've set in the test isn't whether a match is found, but rather the result of the vlookup. Try =if(isna(vlookup(GlobalOpsInput!$B$25,RefTables!$A $130:$B$133,2,FALSE)),"Ops Plant Engineer - "&GlobalOpsInput!$F$21,vlookup(GlobalOpsInput!$B$2 5,RefTables!$A$130:$B$133,2,FALSE)) In words: if the vlookup fails (returns NA), set the result to "Ops Plant Engineer..."; otherwise use the result of the vlookup. "Jani" wrote: I can't figure out where have I gone wrong with this formula: =IF(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130: $B$133,2,FALSE),"Ops Plant Engineer - "&GlobalOpsInput!$F$21) What I am trying to do is if the text is found in B25, then look at RefTable and pull in the data in the second column (which is a formula: ="Director Customer Service - "&RefTables!G121), if not found, then insert "Ops Plant Engineer" with the data in cell F21. If the data is found, the cell with the formula is blank, if it is not found then there is a #VALUE error. Even if I type something in the RefTable second column, the formula still returns a blank. Thanks for your help! jms |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup Displays Blank
Make sure the value isn't
"Ops Plant Engineer "-GlobalOpsInput!$F$21 that would create a value error so you might want to take a look at that part and type it in again an make sure it looks like "Ops Plant Engineer -"&GlobalOpsInput!$F$21 the only other way you can get a value error is if you have the same error in a cell that is involved -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Jani" wrote in message ... You two were sure quick and have solved most of the problem, but I am still getting a #Value error when it doesn't find the condition when it should be entering the "Ops Plant Engineer -" with the value from GlobalOpsInput!$F$21. Any other thoughts??? "bpeltzer" wrote: The condition you've set in the test isn't whether a match is found, but rather the result of the vlookup. Try =if(isna(vlookup(GlobalOpsInput!$B$25,RefTables!$A $130:$B$133,2,FALSE)),"Ops Plant Engineer - "&GlobalOpsInput!$F$21,vlookup(GlobalOpsInput!$B$2 5,RefTables!$A$130:$B$133,2,FALSE)) In words: if the vlookup fails (returns NA), set the result to "Ops Plant Engineer..."; otherwise use the result of the vlookup. "Jani" wrote: I can't figure out where have I gone wrong with this formula: =IF(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130: $B$133,2,FALSE),"Ops Plant Engineer - "&GlobalOpsInput!$F$21) What I am trying to do is if the text is found in B25, then look at RefTable and pull in the data in the second column (which is a formula: ="Director Customer Service - "&RefTables!G121), if not found, then insert "Ops Plant Engineer" with the data in cell F21. If the data is found, the cell with the formula is blank, if it is not found then there is a #VALUE error. Even if I type something in the RefTable second column, the formula still returns a blank. Thanks for your help! jms |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup Displays Blank
Still no luck... this is exactly what the formula is:
=IF(ISNA(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A $130:$B$133,2,FALSE)),"Ops Plant Engineer -"&GlobalOpsInput!$F$21,VLOOKUP(GlobalOpsInput!$B$2 5,RefTables!$A$130:$B$133,2,FALSE)) I have deleted the contents of Cell F21 and made sure is is 'blank' and have tried the formula again but still getting the #Value error. Any further thoughts? "Peo Sjoblom" wrote: Make sure the value isn't "Ops Plant Engineer "-GlobalOpsInput!$F$21 that would create a value error so you might want to take a look at that part and type it in again an make sure it looks like "Ops Plant Engineer -"&GlobalOpsInput!$F$21 the only other way you can get a value error is if you have the same error in a cell that is involved -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Jani" wrote in message ... You two were sure quick and have solved most of the problem, but I am still getting a #Value error when it doesn't find the condition when it should be entering the "Ops Plant Engineer -" with the value from GlobalOpsInput!$F$21. Any other thoughts??? "bpeltzer" wrote: The condition you've set in the test isn't whether a match is found, but rather the result of the vlookup. Try =if(isna(vlookup(GlobalOpsInput!$B$25,RefTables!$A $130:$B$133,2,FALSE)),"Ops Plant Engineer - "&GlobalOpsInput!$F$21,vlookup(GlobalOpsInput!$B$2 5,RefTables!$A$130:$B$133,2,FALSE)) In words: if the vlookup fails (returns NA), set the result to "Ops Plant Engineer..."; otherwise use the result of the vlookup. "Jani" wrote: I can't figure out where have I gone wrong with this formula: =IF(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130: $B$133,2,FALSE),"Ops Plant Engineer - "&GlobalOpsInput!$F$21) What I am trying to do is if the text is found in B25, then look at RefTable and pull in the data in the second column (which is a formula: ="Director Customer Service - "&RefTables!G121), if not found, then insert "Ops Plant Engineer" with the data in cell F21. If the data is found, the cell with the formula is blank, if it is not found then there is a #VALUE error. Even if I type something in the RefTable second column, the formula still returns a blank. Thanks for your help! jms |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup Displays Blank
So if I understand you correctly if there's a match the formula works but if
not it will return the error? Can you copy the workbook, remove all sensitive data and tables etc, make sure you still get the error then email the workbook to remove NOSPAM from the email address -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Jani" wrote in message ... Still no luck... this is exactly what the formula is: =IF(ISNA(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A $130:$B$133,2,FALSE)),"Ops Plant Engineer -"&GlobalOpsInput!$F$21,VLOOKUP(GlobalOpsInput!$B$2 5,RefTables!$A$130:$B$133,2,FALSE)) I have deleted the contents of Cell F21 and made sure is is 'blank' and have tried the formula again but still getting the #Value error. Any further thoughts? "Peo Sjoblom" wrote: Make sure the value isn't "Ops Plant Engineer "-GlobalOpsInput!$F$21 that would create a value error so you might want to take a look at that part and type it in again an make sure it looks like "Ops Plant Engineer -"&GlobalOpsInput!$F$21 the only other way you can get a value error is if you have the same error in a cell that is involved -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Jani" wrote in message ... You two were sure quick and have solved most of the problem, but I am still getting a #Value error when it doesn't find the condition when it should be entering the "Ops Plant Engineer -" with the value from GlobalOpsInput!$F$21. Any other thoughts??? "bpeltzer" wrote: The condition you've set in the test isn't whether a match is found, but rather the result of the vlookup. Try =if(isna(vlookup(GlobalOpsInput!$B$25,RefTables!$A $130:$B$133,2,FALSE)),"Ops Plant Engineer - "&GlobalOpsInput!$F$21,vlookup(GlobalOpsInput!$B$2 5,RefTables!$A$130:$B$133,2,FALSE)) In words: if the vlookup fails (returns NA), set the result to "Ops Plant Engineer..."; otherwise use the result of the vlookup. "Jani" wrote: I can't figure out where have I gone wrong with this formula: =IF(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130: $B$133,2,FALSE),"Ops Plant Engineer - "&GlobalOpsInput!$F$21) What I am trying to do is if the text is found in B25, then look at RefTable and pull in the data in the second column (which is a formula: ="Director Customer Service - "&RefTables!G121), if not found, then insert "Ops Plant Engineer" with the data in cell F21. If the data is found, the cell with the formula is blank, if it is not found then there is a #VALUE error. Even if I type something in the RefTable second column, the formula still returns a blank. Thanks for your help! jms |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup Displays Blank
The error was due to that "transition formula evaluation" was checked under
toolsoptionstransition. This sometimes happen with minor things that are incompatible with regards to Lotus 123 and Excel Since this was a large and rather complicated spreadsheet with lots of interdependent formulas I won't have the time to audit it to pinpoint the exact formula that was the root of the error but since Jani had a lot of text numbers with trailing spaces I am pretty sure somewhere there is the reason. As an example where this option checked returns an error is =DATE(YEAR(TODAY()),MONTH(TODAY())+1,0) will return the last day of today's month but if you select "transition formula evaluation" it will return #NUM! and of course any dependent formula will also return the error -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Peo Sjoblom" wrote in message ... So if I understand you correctly if there's a match the formula works but if not it will return the error? Can you copy the workbook, remove all sensitive data and tables etc, make sure you still get the error then email the workbook to remove NOSPAM from the email address -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Jani" wrote in message ... Still no luck... this is exactly what the formula is: =IF(ISNA(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A $130:$B$133,2,FALSE)),"Ops Plant Engineer -"&GlobalOpsInput!$F$21,VLOOKUP(GlobalOpsInput!$B$2 5,RefTables!$A$130:$B$133,2,FALSE)) I have deleted the contents of Cell F21 and made sure is is 'blank' and have tried the formula again but still getting the #Value error. Any further thoughts? "Peo Sjoblom" wrote: Make sure the value isn't "Ops Plant Engineer "-GlobalOpsInput!$F$21 that would create a value error so you might want to take a look at that part and type it in again an make sure it looks like "Ops Plant Engineer -"&GlobalOpsInput!$F$21 the only other way you can get a value error is if you have the same error in a cell that is involved -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Jani" wrote in message ... You two were sure quick and have solved most of the problem, but I am still getting a #Value error when it doesn't find the condition when it should be entering the "Ops Plant Engineer -" with the value from GlobalOpsInput!$F$21. Any other thoughts??? "bpeltzer" wrote: The condition you've set in the test isn't whether a match is found, but rather the result of the vlookup. Try =if(isna(vlookup(GlobalOpsInput!$B$25,RefTables!$A $130:$B$133,2,FALSE)),"Ops Plant Engineer - "&GlobalOpsInput!$F$21,vlookup(GlobalOpsInput!$B$2 5,RefTables!$A$130:$B$133,2,FALSE)) In words: if the vlookup fails (returns NA), set the result to "Ops Plant Engineer..."; otherwise use the result of the vlookup. "Jani" wrote: I can't figure out where have I gone wrong with this formula: =IF(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130: $B$133,2,FALSE),"Ops Plant Engineer - "&GlobalOpsInput!$F$21) What I am trying to do is if the text is found in B25, then look at RefTable and pull in the data in the second column (which is a formula: ="Director Customer Service - "&RefTables!G121), if not found, then insert "Ops Plant Engineer" with the data in cell F21. If the data is found, the cell with the formula is blank, if it is not found then there is a #VALUE error. Even if I type something in the RefTable second column, the formula still returns a blank. Thanks for your help! jms |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup Displays Blank
Thanks, Peo, with your patience in getting me on the right track! jms
"Peo Sjoblom" wrote: So if I understand you correctly if there's a match the formula works but if not it will return the error? Can you copy the workbook, remove all sensitive data and tables etc, make sure you still get the error then email the workbook to remove NOSPAM from the email address -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Jani" wrote in message ... Still no luck... this is exactly what the formula is: =IF(ISNA(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A $130:$B$133,2,FALSE)),"Ops Plant Engineer -"&GlobalOpsInput!$F$21,VLOOKUP(GlobalOpsInput!$B$2 5,RefTables!$A$130:$B$133,2,FALSE)) I have deleted the contents of Cell F21 and made sure is is 'blank' and have tried the formula again but still getting the #Value error. Any further thoughts? "Peo Sjoblom" wrote: Make sure the value isn't "Ops Plant Engineer "-GlobalOpsInput!$F$21 that would create a value error so you might want to take a look at that part and type it in again an make sure it looks like "Ops Plant Engineer -"&GlobalOpsInput!$F$21 the only other way you can get a value error is if you have the same error in a cell that is involved -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Jani" wrote in message ... You two were sure quick and have solved most of the problem, but I am still getting a #Value error when it doesn't find the condition when it should be entering the "Ops Plant Engineer -" with the value from GlobalOpsInput!$F$21. Any other thoughts??? "bpeltzer" wrote: The condition you've set in the test isn't whether a match is found, but rather the result of the vlookup. Try =if(isna(vlookup(GlobalOpsInput!$B$25,RefTables!$A $130:$B$133,2,FALSE)),"Ops Plant Engineer - "&GlobalOpsInput!$F$21,vlookup(GlobalOpsInput!$B$2 5,RefTables!$A$130:$B$133,2,FALSE)) In words: if the vlookup fails (returns NA), set the result to "Ops Plant Engineer..."; otherwise use the result of the vlookup. "Jani" wrote: I can't figure out where have I gone wrong with this formula: =IF(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130: $B$133,2,FALSE),"Ops Plant Engineer - "&GlobalOpsInput!$F$21) What I am trying to do is if the text is found in B25, then look at RefTable and pull in the data in the second column (which is a formula: ="Director Customer Service - "&RefTables!G121), if not found, then insert "Ops Plant Engineer" with the data in cell F21. If the data is found, the cell with the formula is blank, if it is not found then there is a #VALUE error. Even if I type something in the RefTable second column, the formula still returns a blank. Thanks for your help! jms |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup return 0 when cell is blank | Excel Worksheet Functions | |||
| How do I stop my Pivot tables from spelling out (blank)? | Excel Discussion (Misc queries) | |||
| Replace null string with blank cell | Excel Discussion (Misc queries) | |||
| Summarize Out of Stock List | Excel Worksheet Functions | |||
| Blank Cells in Pivot Tables | Excel Discussion (Misc queries) | |||
Linear Mode
