Advanced Excel Formulas- Many advanced formulas are available in Excel. With some practice, you can become an expert at using these and many other functions to perform complex calculations and analyses in your spreadsheets.
VLOOKUP
This formula works for the specified value in the first column of the table and returns the value in the same row from the specified column. For example, if you have a table of customer information and you want to look up a customer’s address based on their name, you would use the following formula.
=VLOOKUP(A2, customer_table, 2, FALSE)
It looks up the value in cell A2 that contains the customer’s name in the first column of the “customer_table” array and returns the value from the second column (which contains the customer’s address).
Index/Match
This formula is similar to VLOOKUP, but in some cases simpler. INDEX/MATCH looks for a specified value in a table and returns a value from a different column in the same row, but unlike VLOOKUP, it allows you to specify a column to return based on an exclusive match. Here is an example.
=INDEX(sales_data, MATCH(B2, sales_data[Product], 0), MATCH(C2, sales_data[[Month]:[December]], 0))
This formula returns the number of sales for a given product (specified in cell B2) and month (specified in cell C2) based on the table “sales_data”. The first MATCH function finds the row in the table that matches the product name, and the second MATCH function finds the column that matches the month (using the range of columns from “Month” to “December”). The INDEX function returns the value from the intersection of that row and column.
SUMIF/SUMIFS
These formulas allow you to sum values in a range based on specific criteria. SUMIF adds values in a range that meet a single condition, but SUMIFS allows you to specify multiple conditions. For example.
=SUMIF(sales_data[Product], “Widget”, sales_data[Sales])
This formula adds all the sales statistics for the product “widget” into the table “sales_data”.
=SUMIFS(sales_data[Sales], sales_data[Product], “Widget”, sales_data[Region], “North”)
This formula adds all the sales figures for the “Widget” product in the “North” region of the same table based on two different conditions.
COUNTIF/COUNTIFS
These formulas work like SUMIF/SUMIFS, but instead of adding values, they count the number of cells that meet certain criteria. For example.
=COUNTIF(sales_data[product], “widget”)
This formula counts the number of rows in the table “sales_data” that contain the value “product” column “widget”.
=COUNTIFS(sales_data[Product], “Widget”, sales_data[Region], “North”)
This formula counts the number of rows in the same table that has “Widget” in the “Product” column and “North” in the “Region” column.
These are just a few examples of the many advanced formulas available in Excel.