Print Email Facebook Twitter Detecting Problematic Lookup Functions in Spreadsheets Title Detecting Problematic Lookup Functions in Spreadsheets Author Hermans, F. Aivaloglou, E. Jansen, B. Faculty Electrical Engineering, Mathematics and Computer Science Department Software Technology Date 2015-12-31 Abstract Spreadsheets are used heavily in many business domains around the world. They are easy to use and as such enable end-user programmers to and build and maintain all sorts of reports and analyses. In addition to using spreadsheets for modeling and calculation, spreadsheets are often also used for creating reports and dashboards: combining data from different sources and creating overviews. For this, lookup functions can be used: they search for a value in a range and return a corresponding row or column. Lookup functions are common: according to recent research the VLOOKUP is the fifth most common Excel function. In this paper we investigate the use of lookup functions in more detail. We analyze lookup functions within the newly released Enron spreadsheet corpus. The results show that 1) a minority of 43% of lookup formulas use the default setting where an approximate match may be returned, 2) 77% of approximate matches are used unnecessary and 3) 23% of approximate lookups is problematic: they search over unsorted ranges, while this is specifically advised against in the specification, and might lead to wrong results. To reference this document use: http://resolver.tudelft.nl/uuid:4ad537a6-2fcb-4294-adf1-1b8c17cae41d Publisher Delft University of Technology, Software Engineering Research Group ISSN 1872-5392 Source http://swerl.tudelft.nl/bin/view/Main/TechnicalReports Source Report TUD-SERG-2015-011 Preprint accepted for publication in Accepted for publication in the Proceedings of the IEEE Symposium in Visual Languages and Human Centric Computing (VL/HCC 2014) Part of collection Institutional Repository Document type report Rights © 2015 The Author(s) . Software Engineering Research Group, Department of Software Technology, Faculty of Electrical Engineering, Mathematics and Computer Science, Delft University of Technology Files PDF TUD-SERG-2015-011.pdf 287.79 KB Close viewer /islandora/object/uuid:4ad537a6-2fcb-4294-adf1-1b8c17cae41d/datastream/OBJ/view