Advanced C# Excel Functions for Data Analysis - Part 1

Have you ever desired advanced calculation capabilities in your application similar to those in Excel?

Formulas and functions quietly play an important role in data analysis. Microsoft Excel provides a range of functions that simplify the extraction of key metrics during data analysis. However, replicating this functionality in a C# application demands custom logic and a substantial investment of time.

To implement Excel functions and other Excel-like functionalities in your application, Mescius offers Document Solutions for Excel API (DsExcel). DsExcel is a comprehensive .NET spreadsheet API library based on the Excel object model with no dependencies on Excel. It not only lets you create, edit, load, and save Excel files but also lets you perform various computations on your data, just like Excel.

In this blog, we will cover the following Excel functions and demonstrate their usage with the DsExcel API:

• LOOKUP and Reference Functions
• VLOOKUP/ HLOOKUP Functions
• LOOKUP Function
• XLOOKUP Function
• MATCH Function
• FILTER Function
• TRANSPOSE Function

Here, we will use the following JSON sales data to see how to use the above-mentioned functions. You can add this data to your workbook using one of the techniques mentioned here.

Here is how the data is presented in an Excel spreadsheet, following a few additional formatting adjustments for visual appeal:

Download Document Solutions for Excel, .NET Edition now and try them yourself!

LOOKUP and Reference Functions

Lookup and Reference functions dynamically retrieve values or indexes from the datasets. These functions work with arrays of data and return the result based on certain criteria.

Functions in these categories help in a quick analysis of data and save a lot of time by automating repetitive tasks. 

DsExcel supports these Lookup and Reference functions, so you can also make your applications powerful. Let us understand some of them with examples.

VLOOKUP/HLOOKUP Function

VLOOKUP (short for Vertical LookUp) searches for a value in the leftmost column of a specified range and retrieves the value from another designated column of the same row.

Let's break this down with an example. For instance, you can use the VLOOKUP function to find the salesperson who sold a particular product ID from the given data.

Before we dive deeper, let us first understand the syntax of this function and its parameters.

=VLOOKUP(lookup_value, table_array,column_index_num,[range_lookup])

• lookup_value - The first parameter looks for a specified value in the leftmost column of a table. In this example, we are looking for a particular Product ID specified in the K8 cell
• table_array - This parameter represents the target table or cell range that contains the source data to be searched. Let us take B4:E13 as the target range that starts with the Product ID column, which should be the leftmost 
• column_index_num - The third parameter indicates the index of the target column in the table. As we are searching in the Sales Person column, [JS1], the value of this parameter should be 4 
• range_lookup - This is an optional parameter to specify the type of search. You can set it to TRUE for an approximate match (default) and FALSE for an exact match. Here, we are setting it to false

With these parameter values, the final VLOOKUP function looks like below:

=VLOOKUP(K8,B4:E13,4,FALSE)

In DsExcel, set this function in the L8 cell to retrieve the salesperson's name:

//Apply formula to L8 cell of the worksheet
worksheet.Range["L8"].Formula = "=VLOOKUP(K8,B4:E13,4,FALSE)";

As mentioned earlier, VLOOKUP can only search data vertically from the leftmost column. Similarly, for horizontal data, you can use the HLOOKUP (short for Horizontal Lookup) function that searches data in the first row. However, both of these functions only work with lookup data sorted in ascending order.

LOOKUP Function

The LOOKUP function can perform vertical and horizontal searches in the data.

In vertical search, this function looks for a value in a particular column and returns the value of the same row from another specified column. The reverse happens in the case of horizontal search.

Unlike VLOOKUP and HLOOKUP functions, which require an entire search range, the LOOKUP function gives you results by specifying the search and results in columns or rows only. This not only improves the performance but also gives you correct results even if the position of the search column has changed.

To understand this function, let’s take the same example of searching for a salesperson's name for a particular Product ID. But before that, look at the syntax and the parameters of the LOOKUP function:

=LOOKUP(lookup_value, lookup_array,result_array])

• lookup_value - This parameter looks for a specified value in a table's particular column/row. In our case, the K8 cell holds the product ID we seek
• lookup_array - The second parameter represents the column/row that contains the source data to be searched. Here, B4:B13 is the column that contains Product IDs
• result_array - The third parameter indicates the target column/row, the Sales Person column (E4:E13) in this case

After assigning all the values, our LOOKUP function comes out to be:

=LOOKUP(K8,B4:B13,E4:E13)

The DsExcel code below shows how to use the LOOKUP function to find out the salesperson's name:

//Apply formula to L8 cell of the worksheet
worksheet.Range["L8"].Formula = “=LOOKUP(K8,B4:B13,E4:E13)”;

Like VLOOKUP and HLOOKUP functions, LOOKUP provides correct results only if the data is sorted in ascending order. However, it does not deal with the situation if the match is not found. 

XLOOKUP Function

XLOOKUP is the most advanced lookup function introduced in Excel 365 and Excel 2019. This function eliminates all the limitations of the above-mentioned functions. Additionally, it comes with some advanced matching and searching capabilities, such as search direction. 

Let’s extend the last example and search for the salesperson along with the region where he sold the particular product ID. Also, this time, let’s try searching in reverse direction, from bottom to top. To see how we can utilize XLOOKUP to get the desired result, first, see the syntax and parameters of this function:

=XLOOKUP(lookup_value,lookup_array,return_array,[not_found],[match_mode],[search_mode]))

• lookup_value - Similar to previous lookup functions, this parameter also looks for the specified value in the particular column/row of a table, which is K8 in this case
• lookup_array - This parameter represents the column/row range to search for the lookup_value. The product IDs are in the B4:B13 range
• return_array - The third parameter indicates the range for the target values. D4:E13 is the range that contains the Sales Person and Sales Region columns
• not_found -This parameter returns the specified value if no match is found. Let’s set it to “Record Not Found”
• match_mode - The fifth parameter specifies the type of match where 0 is for an exact match, -1 for an exact match or next smaller item, 1 for an exact match or next larger item, and 2 for a wildcard match. We are looking for the exact value, so set it to 0
• search_mode - The last parameter defines the direction of search where 1 is for performing a search from the top, -1 for the bottom, 2 for the binary search when data is in ascending order, and -2 is for the binary search when data is in descending order. As per our use case, it is set to -1

After putting all these parameters in the XLOOKUP function, it appears as follows:

=XLOOKUP(K8,B4:B13, D4:E13,”Record Not Found”,0,-1)

The following DsExcel code shows how to retrieve the salesperson and the region name using the XLOOKUP function:

//Apply formula to L8 cell of the worksheet
worksheet.Range["L8"].Formula = "=XLOOKUP(K8,B4:B13, D4:E13,”Record Not Found”,0,-1)";

MATCH Function

The MATCH function searches for a specified item in a range and returns its index. 

Using this function, you can make other functions dynamic to give you the desired result automatically, even if the data structure changes. Let’s understand this with the previous use-case of the VLOOKUP function, where the column_index_num parameter was set to a hard-coded value. If you change the position of the Sales Person column in the data, the VLOOKUP won’t return the correct value. Here, the MATCH function can get the index of the Sales Person column dynamically.

But before we move on, let's look at the syntax and parameters of the MATCH function.

=MATCH(lookup_value, lookup_array, [match_type])

• Lookup_value - The first parameter searches the specified value in the range. Let’s set it to “Sales Person”
• Lookup_array - This parameter defines the column/row to search the item index. Here, we are performing a search in the B3:E3 range
• Match_type - Using this parameter, you can specify the type of search where 0 is used for the exact match, -1 is for the closest match above the lookup value, and 1 is for the closest match below the lookup value. We set it to 0

After putting all the parameters, the below function is formed:

=MATCH("Sales Person",B3:E3,0)

In the VLOOKUP function, just replace the column_index_num parameter with the MATCH function above to make it dynamic. 

=VLOOKUP(K8,B4:E13,MATCH("Sales Person",B3:E3,0),FALSE)

Now, the VLOOKUP formula will give the same result even if the index of the Sales Person column is changed.
Below is the final DsExcel code to show the usage of the MATCH function with the VLOOKUP function:

//Apply formula to L8 cell of the worksheet
worksheet.Range["L8"].Formula = “=VLOOKUP(K8,B4:E13,MATCH("Sales Person",B3:E3,0),FALSE)”;

FILTER Function

The FILTER function lets you extract data based on certain criteria. This function is ideal when you need a part of data from a large dataset based on specific conditions.

Let’s fetch the sales records in the North Region. Here is the syntax of the FILTER function:

=FILTER(array,include,[if_empty])

• Array - The parameter defines the range of values to be filtered. Set it to A4:G13 to search all the records
• Include - The second parameter is a boolean array of conditions. In our case, we are looking for the “North” region in D4:D13; that is, the value of this parameter should be D4:D13="North"
• If_empty - This parameter returns a specified value when no entries meet the criteria. We set it to “No Record Found”

Below is the FILTER function with all these parameter values:

=FILTER(A4:G13,D4:D13=” North”,"No Record Found")

The DsExcel code below shows how to use the FILTER function to get the details of the items sold in the North region:

//Apply formula to K8 cell of the worksheet
worksheet.Range["K8"].Formula = “=FILTER(A4:G13,D4:D13=”North”,"No Record Found");

TRANSPOSE Functions

The TRANSPOSE function changes the orientation of the given data. It transforms the vertical data into a horizontal layout or vice versa, which helps analyze data from a different perspective. The syntax of this function is quite simple, as it just takes the range to be transposed.

=TRANSPOSE(array)

Say we want to analyze monthly sales; transposing the data and having months as column headers makes it easier to analyze the data for each month. Here is the TRANSPOSE function with the target range as its only parameter:

=TRANSPOSE(A3:G13)

Use the DsExcel code below for transposing the data:

//Apply formula to K3 cell of the worksheet
worksheet.Range["K3"].Formula = “=TRANSPOSE(A3:G13)”;

You can also download the sample to see all the above-explained functions in action. 

Conclusion

In this blog, you've discovered how to enhance your data analysis capabilities in your .NET applications by incorporating advanced Excel functions without custom logic. DsExcel, the sophisticated spreadsheet API, goes beyond by providing a plethora of Excel-like features, including filtering, sorting, pivot tables, conditional formatting, charts, sparklines, and much more.

Download Document Solutions for Excel, .NET Edition now and try them yourself!

More References:

• Product Demo .NET
• Documentation .NET
• Product Demo JAVA
• Documentation JAVA