If you have ever wanted to share your Excel spreadsheet with a user of another spreadsheet package, you know everything goes fine until you get to a macro or formula that relies on a function that exists in Excel but was not ported to the other package. Frustrating!
In this article we will show how we can use ‘user defined functions’ to create functions that exist in Microsoft Excel but do not exist in another spreadsheet platform, allowing you to share many more spreadsheets without the hassle. We will take example of “Lookup” function of Microsoft Excel to illustrate how it is done.
I am going to use Excel to Google Docs as the example. Essentially the process looks like this:
- Share your spreadsheet only to find a function that exists in Excel and not in Google docs.
- Write a script in google docs that performs the missing functon.
- View the spreadsheet in Google Docs with the new function in place.
Which Excel Functions is Google Docs Missing?
After a bit of research we found a few popular functions that exist in Excel but not in Google’s Spreadsheet software. Here are few examples:
- CELL() and INFO() – retrieve system information (OS, Platform, Office, Version) and cell properties (color, width, etc)
- CLEAN() Cleans a string of unprintable characters (specifically chars 0 through 31)
- CONVERT() Converts between various units (mass, length, force, etc)
- LOOKUP() Old function that looks up a value. This function exists for backward compatibility. HLOOKUP and VLOOKUP replaced it.
There are many more. Luckily the most used ones were implemented by Google engineers.
Some of the missing functions are related to data types that do not exist in Google Spreadsheet such as complex numbers (for example, the COMPLEX() function).
Some are new functions in Office 2007 such as COUNTIFS(), which are not implemented in Google Docs yet but may be at some point.
Others are pretty unusual or niche, yet you might have an Excel file that uses them anyway (like CUBEMEMBER() ).
Bottom line, Google folks did a really good job of implementing much of the most popular Excel functionality, but it is not much help knowing your function is rarely used if you really need it!
Our Example: How to Create Lookup Function in Google Docs
Imagine I have this “old” Excel file that uses the function LOOKUP, in order to determine if a certain value appears in a list. If product number 5 appears in the list, then I have a cell that changes to “yes”, otherwise it shows “no”.
The formula assumes that a call to LOOKUP with just two parameters returns the searched value if found, or 1 if not found.
When I load this file into Google Spreadsheet, the formula in cell B8 doesn’t work and instead shows the dreaded #NAME? error, because LOOKUP isn’t supported in Google Docs.
Of course I could adapt the formula in this specific cell to use the VLOOKUP function instead, but what if my sheet uses LOOKUP many times in plenty of different formulas?
I often say the best programmers are “lazy” because we will program our way out of expending unnecessary manual effort! It would certainly be easier to emulate LOOKUP in Google Docs, so everything just works as-is, instead of modifying each and every occurrence.
Also, if I have plenty of other similar documents, I could use the same user-defined LOOKUP function in Docs for all of them.
Sound good? OK, let’s do it!
Creating Your User Defined Function
We start building our function by opening the script editor:
In the script editor, we delete myFunction() and add my simple LOOKUP implementation instead:
function LOOKUP(value, range) {
for (var i = 0; i < range.length; i++) {
if (range[i]== value) {
return value;
}
}
return 1;
}
Next, save the script under any meaningful name you want.
Now if we go back to the sheet, just like magic … it works!
To save complexity and unnecessary work, only the required functionality of the missing function should actually be implemented. The rest is not necessary and can be added later if you find you need it.
Summary
While most of the functions that you rely on day to day can usually be found in competing spreadsheet packages, every so often you try to share a spreadsheet only to find something vital is missing. When that happens you know you now have an opportunity to add in the missing function and keep working!
About the author
Yoav Ezer co-authors the blog Codswallop. He is also the CEO of a company that produces PDF to XLS converter software.
For more Excel tips from Yoav, join him on Facebook or Twitter
