Here at Zoho Sheet, we know that functions are an integral part of any spreadsheet application. With more than 400 predefined functions, our team is still constantly working to improve the functions gallery even more. In this update, we’re happy to introduce XLOOKUP, XMATCH, and six other new functions in Zoho Sheet.
XLOOKUP
For a long time, VLOOKUP has been one of the most popular functions among spreadsheet users. But, VLOOKUP has its share of limitations too—namely, single direction search. With this update, we might just see the VLOOKUP function replaced in popularity!
Our new XLOOKUP function can be called an improved version of both the VLOOKUP and HLOOKUP functions. This function searches for a value in the given column or row, and returns the relevant value from a specified result table. The result can be a single value or an array of values based on the result table chosen. Since this function lets you choose the result table, there won’t be any trouble while inserting columns/rows in the range too.
Not just that, XLOOKUP gives you the complete power to customize the search and match types based on your needs. Also, with XLOOKUP, you get to provide a custom value or text to be displayed when the appropriate match is not found in the given data range. View syntax
XMATCH
An advanced version of the MATCH function, XMATCH is similar in behavior to XLOOKUP and provides the index of the relevant value as the result. It also improves on MATCH by supporting reverse search. View syntax
SORT and SORTBY
Organizing your data makes it easier to observe and analyze it at a glance. While the SORT function helps you sort a given range by a single column, SORTBY helps you order the chosen data range based on more than one column, which can also be a range that is not a part of the selected data. Both SORT and SORTBY are dynamic array functions, meaning the size of the resultant range may change based on the source range. View syntax
RANDARRAY
Why manually repeat the RAND functions for every row and column when you can do it in a single step? We’re taking the RAND functions to the next level with RANDARRAY, which you can use to generate a random array with a specified number of columns and rows, highest and lowest values, and the format of the number, (whole or decimal). So, next time you create a sample spreadsheet, save time creating the data with RANDARRAY. View syntax
SEQUENCE
Now that RANDARRAY helps you generate random data with ease, what about an ordered data array? The SEQUENCE function helps you create an array with sequential numbers. You can choose to specify the number of rows and columns to fill, the start value, and the number by which the subsequent entries must increase. View syntax
RANK.EQ and RANK.AVG
We’re also introducing two improvements on the RANK function: RANK.EQ and RANK.AVG. While both the RANK.EQ and RANK.AVG functions return the rank of a value from a given array, a minor difference between them appears when more than one value has the same rank. The former returns the highest rank in the given set of ranks, while the latter returns the average of the ranks. For example, if two numbers have the same rank 2, then RANK.EQ returns 2 for both, while RANK.AVG returns 2.5 for both (average of 2 and 3). View syntax
All these new functions are now available for all Zoho Sheet users—for free! Try them out today and leave your feedback as comments below. Need help with the functions? Drop us an email at support@zohosheet.com, and we’ll be happy to help you out.
Could sequence also be used to populate time so the example give 2 days, could you populate every 3 hours for example?
Hi Stanley, Sorry for the late reply. Yes, you can use the SEQUENCE function to populate time in hours too. You can follow the below syntax: =SEQUENCE(5;1;E21;VALUE("3:00")) Here, the cell E21 contains the start time (in hours), and "3:00" represents the increment value. This function will fill 5 rows in a single column. You can replace the parameters with your desired values. Feel free to write back if you have any questions. Thank you!