Here is the list of all excel functions I use daily during my work day. These functions can be applied not only to the data analysts but also to other roles such as accountants, sales representatives, etc. Here is the list:
XLOOKUP
MATCH
INDEX
SUMIF/SUMIFS
IFERROR
VALUE
This function is my number one go to function working with data. This function looks for the value in the rows and in case of a match it returns the value in the same row out of different column. I used to use VLOOKUP which is the previous version. However, this function took longer to set up, so I started using the XLOOKUP which in my opinion is easier to set up.
There are required parameters of the function such as lookup_value - the value you are looking for to match; lookup_array - its the column in which you do the look up (mostly in the different data table); return_array - a column from which you are taking the data; if_not_found - the option in which you can set the default value, which will show in case of no matching value to be found; match_mode - this parameter defines the mode in which you search for values (-1 - exact match, if not taking the lower value, 0 - exact match, 1 - exact match, if not taking the higher value, 2 - using the *, ? and ~ with special meaning); search_mode - this mode defines in which order the search for values will be performed (-1 - from the bottom to the top, 1 - from the top to bottom, 2 - binary search with expected search values to be sorted out in ascending order, -2 - same as num. 2, but with expected search values to be sorted out in descending order).
Tips: I used this function many times, for example when I combine two datasets and I was looking for missing values but at the same time not making any duplicate values.
This function search for a specified value and returns the position of the value (this position is relative to the searched area). I use this function mostly with the INDEX function which returns the value you look for based on the index (relative position). The search can be done on columns or rows.
There are required parameters of the function such as lookup_value - the value you are looking for to match; lookup_array - its the column or row in which you do the look up (mostly in the different data table);
This function returns the specific value based on a specified index of the cell in excel (relative to the specified area). This function is also good to use with the function Match, which I have mentioned above. The reason is simple, I tend to be lazy and I want to do my work more efficiently, so I try to automatize my work as much as I can.
There are required parameters of the function such as array - in which the function looks for the relative position (if you combine the match function the area should be included in the array); row_num - there you can put the relative position from the match function to specify in which row the index should take the value from (also you can put the static number of a row and use the other parameter to search for value); column_num - this parameter specifies the column in which should be the value searched. You can also put the static number of a column or use the relative position from the match function.
This function sums the values which you specify based on the same value you look for. Usually, the same key which you want to sum up. This function can be used if you have the data in a non-aggregated form and you want to sum the values into a different table. It is good to consider the use of a pivot table because it could be possibly an efficient way.
There are required parameters of the function such as range - in which you specify the area of search for the specific value; criteria - this parameter is the value which is being looked for (in this case the second quarter in the table area); sum_range - this parameter specify the area that will be sum up if the searched value is same as the value in searched area.
This function can be life-changing in case you are building so robust report and you use division a lot. In the case of any error, this function will fill in the error message with the predefined value which is set within the function. I combine this function with other functions to get rid of any issues.
There are required parameters of the function such as value - this parameter specifies the cell which is being checked for errors; value_if_error - this parameter specifies the value which will be set if the previous value will contain the error.
I use this function when I work with value datasets and I want to use other functions such as xlook up to pair up more tables or datasets. Excel also gets data types sensitive sometimes so I combine this function with the other functions to make the lookup parameter the same data type. This saves me time that I would have to spend changing the data types and cleaning the data.
There are required parameters of the function such as text - this parameter takes the value and converts it into the value.
Hopefully, this article help you with your Excel work and you will be able to work with your data to find insight.
Matěj Srna
Comments