Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Replace Lookup errors
I'm trying to replace two error messages (#N/A and #VALUE!) found throughout
my spreadsheet. The error messages are a result of lookups that cannot find values on my look up tables. I would like those cells to show up as blank for reporting purposes. I tried to do a find and replace. I did a find ...look in...values...find all. The cells were all found. I leave the replace box blank and click on replace all and get an error message. After trying several times, I find that the "look in" box on the replace page no longer has values highlighted and fact values doesn't even show up. How can I replace all the look up error messages with blank cells? -- Julie |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Replace Lookup errors
Change your formula to
=IF(ISNA(VLOOKUP()),"",VLOOKUP()) obviously put your real data in there with regards to the value error, that is not because of the lookup, it's because somewhere you are trying to calculate with text. You need to find that and correct it. Press F5, special and select formulas and deselect everything but errors, that will select all formulas with errors -- Regards, Peo Sjoblom "Julie" wrote in message ... I'm trying to replace two error messages (#N/A and #VALUE!) found throughout my spreadsheet. The error messages are a result of lookups that cannot find values on my look up tables. I would like those cells to show up as blank for reporting purposes. I tried to do a find and replace. I did a find ...look in...values...find all. The cells were all found. I leave the replace box blank and click on replace all and get an error message. After trying several times, I find that the "look in" box on the replace page no longer has values highlighted and fact values doesn't even show up. How can I replace all the look up error messages with blank cells? -- Julie |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Replace Lookup errors
You can do it within your formulae, testing to see if an error results
and returning something else if it does. This kind of construct: =IF(ISNA(your_lookup),"",your_lookup) or =IF(ISERROR(your_formula),"",your_formula) The first one only traps the #NA error (which is what lookup returns if there is no match), whereas the second will trap all errors. If you are getting a #VALUE error, though, you are probably trying to perform some arithmetic on a text value (maybe returned from your lookup, so the lookup itself is not necessarily at fault). Hope this helps. Pete On Jul 13, 4:28 pm, Julie wrote: I'm trying to replace two error messages (#N/A and #VALUE!) found throughout my spreadsheet. The error messages are a result of lookups that cannot find values on my look up tables. I would like those cells to show up as blank for reporting purposes. I tried to do a find and replace. I did a find ...look in...values...find all. The cells were all found. I leave the replace box blank and click on replace all and get an error message. After trying several times, I find that the "look in" box on the replace page no longer has values highlighted and fact values doesn't even show up. How can I replace all the look up error messages with blank cells? -- Julie |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Replace Lookup errors
Thank you so much - worked great! Being a bit of a novice, showing me the
extra closed parenthesis was especially helpful. Have a great day! -- Julie "Peo Sjoblom" wrote: Change your formula to =IF(ISNA(VLOOKUP()),"",VLOOKUP()) obviously put your real data in there with regards to the value error, that is not because of the lookup, it's because somewhere you are trying to calculate with text. You need to find that and correct it. Press F5, special and select formulas and deselect everything but errors, that will select all formulas with errors -- Regards, Peo Sjoblom "Julie" wrote in message ... I'm trying to replace two error messages (#N/A and #VALUE!) found throughout my spreadsheet. The error messages are a result of lookups that cannot find values on my look up tables. I would like those cells to show up as blank for reporting purposes. I tried to do a find and replace. I did a find ...look in...values...find all. The cells were all found. I leave the replace box blank and click on replace all and get an error message. After trying several times, I find that the "look in" box on the replace page no longer has values highlighted and fact values doesn't even show up. How can I replace all the look up error messages with blank cells? -- Julie |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Replace Lookup errors
If I could bother you with one more question, please. As I said in my last
post, your answer worked perfectly for me. Now, I need to know what to do when my lookup puts a zero (0) in a cell when the lookup found a blank cell. I would like it to be blank if the lookup found a blank cell. Can you help me with that too?-- Julie "Peo Sjoblom" wrote: Change your formula to =IF(ISNA(VLOOKUP()),"",VLOOKUP()) obviously put your real data in there with regards to the value error, that is not because of the lookup, it's because somewhere you are trying to calculate with text. You need to find that and correct it. Press F5, special and select formulas and deselect everything but errors, that will select all formulas with errors -- Regards, Peo Sjoblom "Julie" wrote in message ... I'm trying to replace two error messages (#N/A and #VALUE!) found throughout my spreadsheet. The error messages are a result of lookups that cannot find values on my look up tables. I would like those cells to show up as blank for reporting purposes. I tried to do a find and replace. I did a find ...look in...values...find all. The cells were all found. I leave the replace box blank and click on replace all and get an error message. After trying several times, I find that the "look in" box on the replace page no longer has values highlighted and fact values doesn't even show up. How can I replace all the look up error messages with blank cells? -- Julie |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Replace Lookup errors
Try this:
=IF(COUNTIF(A2:A100,A1),SUBSTITUTE(VLOOKUP(A1,A2:B 100,2,0),0,""),"") "Julie" wrote: If I could bother you with one more question, please. As I said in my last post, your answer worked perfectly for me. Now, I need to know what to do when my lookup puts a zero (0) in a cell when the lookup found a blank cell. I would like it to be blank if the lookup found a blank cell. Can you help me with that too?-- Julie "Peo Sjoblom" wrote: Change your formula to =IF(ISNA(VLOOKUP()),"",VLOOKUP()) obviously put your real data in there with regards to the value error, that is not because of the lookup, it's because somewhere you are trying to calculate with text. You need to find that and correct it. Press F5, special and select formulas and deselect everything but errors, that will select all formulas with errors -- Regards, Peo Sjoblom "Julie" wrote in message ... I'm trying to replace two error messages (#N/A and #VALUE!) found throughout my spreadsheet. The error messages are a result of lookups that cannot find values on my look up tables. I would like those cells to show up as blank for reporting purposes. I tried to do a find and replace. I did a find ...look in...values...find all. The cells were all found. I leave the replace box blank and click on replace all and get an error message. After trying several times, I find that the "look in" box on the replace page no longer has values highlighted and fact values doesn't even show up. How can I replace all the look up error messages with blank cells? -- Julie |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Replace Lookup errors
Use ISNA and IF to find out if the formula is resulting in an error.
For example: =IF(ISERROR(VLOOKUP(D10,E6:E10,1,0)),"",VLOOKUP(D1 0,E6:E10,1,0)) In Excel 2007, there is a function IFERROR, which eliminates the need of typing the formula again. "Julie" wrote in message ... I'm trying to replace two error messages (#N/A and #VALUE!) found throughout my spreadsheet. The error messages are a result of lookups that cannot find values on my look up tables. I would like those cells to show up as blank for reporting purposes. I tried to do a find and replace. I did a find ...look in...values...find all. The cells were all found. I leave the replace box blank and click on replace all and get an error message. After trying several times, I find that the "look in" box on the replace page no longer has values highlighted and fact values doesn't even show up. How can I replace all the look up error messages with blank cells? -- Julie |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Replace Lookup errors
Can the lookup return a zero deriving from a zero in the lookup column, if
not you can simply use a custom format to hide the zeros General;-General; or 0.00;-0.00; can of course be done with currency format as well will not display zeros that way you don't have to change the formulas -- Regards, Peo Sjoblom "Julie" wrote in message ... If I could bother you with one more question, please. As I said in my last post, your answer worked perfectly for me. Now, I need to know what to do when my lookup puts a zero (0) in a cell when the lookup found a blank cell. I would like it to be blank if the lookup found a blank cell. Can you help me with that too?-- Julie "Peo Sjoblom" wrote: Change your formula to =IF(ISNA(VLOOKUP()),"",VLOOKUP()) obviously put your real data in there with regards to the value error, that is not because of the lookup, it's because somewhere you are trying to calculate with text. You need to find that and correct it. Press F5, special and select formulas and deselect everything but errors, that will select all formulas with errors -- Regards, Peo Sjoblom "Julie" wrote in message ... I'm trying to replace two error messages (#N/A and #VALUE!) found throughout my spreadsheet. The error messages are a result of lookups that cannot find values on my look up tables. I would like those cells to show up as blank for reporting purposes. I tried to do a find and replace. I did a find ...look in...values...find all. The cells were all found. I leave the replace box blank and click on replace all and get an error message. After trying several times, I find that the "look in" box on the replace page no longer has values highlighted and fact values doesn't even show up. How can I replace all the look up error messages with blank cells? -- Julie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
where to put results of find operation in find and replace functio | Excel Worksheet Functions | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
Find and replace with a lookup | Excel Worksheet Functions | |||
To find errors | New Users to Excel | |||
find replace cursor default to find box | Excel Discussion (Misc queries) |