Coefficient enables users to import data from various sources. Once you have the data in Google Sheets/Excel, you may need to marry, manipulate, or format the data for further analysis. This is where formulas may be very helpful. Below are some of the common formulas that can be very helpful with your Coefficient imported data.
🚨 When adding formulas to your imported sheet(s), make sure that you place formulas to the right of the imported data. This will avoid issues with the imports and formulas breaking/resulting in errors. 🚨
Formulas
VLOOKUP
VLOOKUP (VL) stands for “Vertical Look Up”. This formula helps you look for a specific value by searching for it vertically across a sheet. This can be done on the same sheet or from another sheet or workbook.
ℹ️ NOTE: the order of your columns matters for this formula - the value being searched must be in the left-most column of the sheet, it will not work if is on the right side.
Format:
=VLOOKUP(search_key, range, index, [is_sorted]) (Google Sheets)
=VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup]) (Excel)
- search_key or lookup_value: The value to search for in the first column of the range.
- range or table_array: Refers to the range within the sheet you want to search.
- index or col_index_num: The index of the column with the return value of the range. Simply put, it is the column number within the range specified that will contain the resulting value. The index must be a positive integer. (If I have 4 columns in my range and the results are in the second column, I would put a “2” here).
-
is_sorted or range_lookup: (This is OPTIONAL in the formula) This designates if the value should be an exact match or an approximate.
- “False” = Exact Match (Most Common and Recommended)
- “True” = Approximate Match (Default is not specified). Before you use this option, you will want to sort your search key in ascending order, or there may be incorrect results in your values returned.
Example: We want to pull in the “Price” values from the “Product Variants” import into the “Products” import sheet.
1. On your “Products” sheet, decide where you want to place the formula (F2) and insert your header (”Price”).
Google Sheets: |
Excel: |
2. Insert your formula in the topmost cell in that column (F3). The formula should look something like this:
=VLOOKUP(A3,' Product Variants'!A:B, 2, FALSE) (Google Sheets)
OR
=VLOOKUP(A3; 'Product Variants'!A:B; 2; 0) (Excel)
The value will populate in the cell (F3). To apply the formula to the entire column, you will need to drag it down the remaining rows till you reach the bottom of your data range.
Google Sheets:
Excel:
Here are the formula values broken down:
- search_key or lookup_value = A3 (The Product ID value)
- range or table_array = ‘Product Variants’!A:B (this is the range in the sheet we are looking for the Price)
- index or col_index_num = 2 (the Price is found in the 2nd column in our “Product Variants” import)
- is_sorted or range_lookup = 0 or FALSE (this means we want the exact value)
Google Sheets: |
Excel: |
3. Now you should see the Price values populated in your sheet (Column F).
ℹ️ PRO TIP: Enable the Formula Auto-Fill Down feature in Coefficient, so that when your sheet refreshes and new values are added to the sheet the VLOOKUP pulls in the Price values for those Products into column F as well. 😎
Google Sheets: |
Excel: |
Check out our Blog about VLOOKUP here!
INDEX
INDEX formulas are used when you want to return values with a specified range of rows and columns (intersection).
Format:
=INDEX(reference, [row], [column]) (Google Sheets)
=INDEX(array, row_num, [column_num]) (Excel)
- reference or array - The range of cells from which the values are returned.
- row or row_num- [OPTIONAL - 0 by default] - The index of the row to be returned from within the reference range of cells.
- column or column_num - [OPTIONAL - 0 by default] - The column index to be returned from within the reference range of cells.
Example: Look up the Contact’s First Name in our Salesforce import appearing in Row 14. (First Name is in column B)
1. In our “Contacts” import from Coefficient, select a column to the right of the import for our formula. In this case, column F. Add your INDEX formula just under your header (F3).
The formula will look like this:
=INDEX(A3:D24,14,2) (Google Sheets)
OR
=INDEX(A3:D24,14,2) (Excel)
Google Sheets:
Excel:
Here are the formula values broken down:
- A3:D24 = the range of cells to search.
- 14 = the row in the range specified above to search.
- 2 = the column in the range specified above to search.
Google Sheets: |
Excel: |
2. Now you should see the value of your INDEX in F3, “Josh”. (it is the value in the sheet's 14th Row and 2nd Column referenced).
Google Sheets: |
Excel: |
Check out our Blog about INDEX here!
MATCH
The MATCH function scans your dataset for a specific value and returns its position using the format below.
Format:
=MATCH(search_key, range, [search_type]) (Google Sheets)
=MATCH(lookup_value, lookup_array, [match_type]) (Excel)
- search_key or lookup_value is the record or value you want to find
- range or lookup_array refers to the column or row you want the function to search
- search_type or match_type [OPTIONAL - 0 by default] defines whether the match should be approximate or exact. If omitted, the default is one (1).
Example: Look up Contacts with the Last Name of “Davis” in the same Contacts import.
1. From the Contacts import from Coefficient, add the MATCH formula in cell F3.
ℹ️ NOTE: Formulas on Coefficient imports should appear to the right of the import range.
=MATCH("Davis", C3:C24, 0) (Google Sheets AND Excel)
Google Sheets:
Excel:
Here are the formula values broken down:
- search_key = “Davis”
- range = C3:C24
- search_type = 0
Google Sheets: |
Excel: |
2. The formula result is “14”, which refers to the position value of our search_key “Davis”. (the 14th row in the column range we specified) 🎉
Google Sheets: |
Excel: |
INDEX MATCH
When using INDEX MATCH in a single formula, this works similarly to a VLOOKUP, except you specify the columns to search and return the value in separate ranges. However, you won’t encounter the errors that VLOOKUP generates.
We will use the MATCH function to identify which row number contains the Opportunity ID= “0065i000006ct80AAA” and then return the Account Name corresponding to the cell on that row.
Format:
=INDEX(reference, MATCH(search_key, range, search_type)) (Google Sheets)
=INDEX(array, MATCH(lookup_value,lookup_array,[match_type]) (Excel)
- reference or array is the range of cells from which the values are returned.
- search_key or lookup_value is the record or value you want to find
- range or lookup_array refers to the column or row you want the function to search
- search_type or match_type is an optional parameter that defines whether the match should be approximate or exact. If omitted, the default is one (1).
Example: We’ll use the All Salesforce Opportunities import and capture the Account Name (Column A) with Opportunity ID (Column E) = 0065i000006ct80AAA.
1. On cell G5, add the INDEXMATCH formula to capture the Account Name.
=INDEX(A3:A35;MATCH(E11;E3:E35;0)) (Google Sheets)
OR
=INDEX(A3:A35,MATCH(E11,E3:E35,0)) (Excel)
Google Sheets:
Excel:
Here are the formula values broken down:
- reference = A3:A35
- search_key = E11
- range = E3:E35
- search_type = 0
Google Sheets: |
Excel: |
2. The result shows “University of AZ Installations”. We used the value returned from the MATCH function to show the account name for Opportunity ID: 0065i000006ct80AAA. 👍🏼
Google Sheets: |
Excel: |
ARRAY
The Array Formula function in Google Sheets applies a single formula to every cell in your pre-defined data range. The function turns your original formula into an array, allowing you to write only one formula and use it across multiple rows.
To simplify, enter the formula in the first cell, specify the array size, and apply the function to each cell within that range. The Array formula function outputs values across multiple columns and rows.
ℹ️ NOTE: Excel does support array formulas, but they require specific syntax and often need to be entered using Ctrl+Shift+Enter (CSE's). Excel does have functions like SUMPRODUCT and INDEX for ARRAY operations. With Dynamic ARRAYs in Excel 365, users have more flexibility in creating and using ARRAYS.
Format:
=ARRAYFORMULA(array_formula) (Google Sheets)
=Add the formula to your cell, then press CTRL or CMD + SHIFT + ENTER on your keyboard. (Excel)
Example: We’ll use the INDEX and ARRAY FORMULA functions from the “All SF Opportunities import” to capture all the information for Account Name = "Grand Hotels Kitchen Generator" and return the values of the entire row of cells (Column A-E).
1. On cell F3, add the ARRAY formula we’ll use to capture all the values of row 26. To achieve the same result in Excel, we'll use the INDEX formula in cell A38.
Google Sheets: |
Excel: |
Here are the formula values broken down:
=ARRAYFORMULA(INDEX(A3:E35;24;0) (Google Sheets)
=INDEX(A3:E35, 27, COLUMN(A:E)) (Excel)
Google Sheets: |
Excel: |
2. The results show all the values on a new row containing all the information for “United Oil SLA” from the designated range. 🎉
Google Sheets:
|
Excel: |
3. Another way to use the ARRAY FORMULA would be to populate all the values within the cells of a specified range.
Google Sheets: =ARRAYFORMULA(INDEX(A3:A35;0;0)) |
Excel: =INDEX(A3:E35,1,1) |