Data lookup is one of the most common functions used in spreadsheets. This way, you can pull product information, pricing, ID numbers, or any other data that corresponds to an item. Traditionally done using the VLOOKUP and HLOOKUP functions, being able to search and use data inside large tables can be very useful.
Despite their popularity, these two lookup functions can be hard to understand at first for many users, and one of the reasons for this is the unique limitations of both formulas. For example, while VLOOKUP can only do a vertical search in a table, HLOOKUP can only do a horizontal search. Another limitation is that both formulas can only search and return results from the same table and need to use an index number for the return value.
While Microsoft will be keeping support for the older VLOOKUP and HLOOKUP functions for backwards-compatibility, the new XLOOKUP function is intended to replace the two legacy data lookup formulas. This new function not only solves some of the limitations of its older cousins, but it also combines them in a single function and brings more features to the table.
For instance, you can now set the lookup and return areas as two separate columns, instead of finding and returning data from the same table. Another useful feature of the XLOOKUP function is the ability to set a search mode. With this, you can have the formula return the next largest or smallest result. Finally, you can add a value to be returned if no results are found. This essentially adds the features of an error-handling formula like IFERROR, without having to add another nested formula to the XLOOKUP function.
SpreadsheetWeb’s 6.25 version adds support for the new and powerful XLOOKUP function. You can now utilize this function in your workbooks and do more with your data lookup queries using fewer formulas. This results in even better calculation performance and makes it easier to create and maintain the calculation logic in your workbooks.
Let’s take an example to see how the new XLOOKUP function revolutionizes the way data lookup is done in workbooks. For this, we are going to be using a product lookup workbook as shown below.
This workbook consists of hundreds of products, their descriptions, and price information. Even though Excel and SpreadsheetWeb can handle these types of lookups very quickly, using XLOOKUP instead further improves performance, and the system can bring the results in an instant.
In addition to some bug fixes, performance improvements, and enhancements our trial AI tool, we’ve also added Zendesk integration. Zendesk is a popular CRM platform that aims to improve the customer service and sales processes, by providing businesses with a scalable solution. With SpreadsheetWeb for Zendesk, your sales teams can run complex calculations directly inside your favorite CRM, without going back-and-forth multiple platforms.
We hope that you’ll like these amazing new features as much as we do! For a full list of changes, updates, and new features, please see the change log.
You can always sign up for a 14-day free account and start creating robust web applications using only your Excel workbooks.
