Spreadsheet Formulas

Section header image mobile

Coefficient enables users to import data from various sources. Once you have the data all in Google Sheets, 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. 🚨

VLOOKUP

INDEX

MATCH

INDEX MATCH

ARRAY

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])

  • search_key: The value to search for in the first column of the range.
  • range: Refers to the range within the sheet you want to search.
  • index: The index of the column with the return value of the range. To put it simply, 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: (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”).vlookup_ss1.png

2. Insert your formula in the topmost cell in that column (F3). The formula should look something like this: =VLOOKUP(A3; 'Product Variants'!$B:$C; 2; FALSE). 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.vlookup_ss2.png

Here are the formula values broken down:

  • search_key = A3 (The Product ID value)
  • range = ‘Product Variants’!A:B (this is the range in the sheet we are looking for the Price)
  • index = 2 (the Price is found in the 2nd column in our “Product Variants” import)
  • is_sorted = 0 or FALSE (this means we want the exact value)vlookup_ss3.png

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. 😎 vlookup_ss4.png

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])

  • reference - The range of cells from which the values are returned.
  • row - [OPTIONAL - 0 by default] - The index of the row to be returned from within the reference range of cells.
  • column - [OPTIONAL - 0 by default] - The index of the column 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;12;2)index_ss1.png

Here are the formula values broken down:

  • A3:D24 = the range of cells to search.
  • 12 = the row in the range specified above to search.
  • 2 = the column in the range specified above to search.index_ss2.png

2. Now you should see the value of your INDEX in F3, “Josh”. (it is the value in the 12th Row and 2nd Column of the sheet referenced).index_ss3.png

Check out our Blog about INDEX here!

MATCH

The MATCH function scans your dataset for a specific value and returns its position with the below format.

Format: =MATCH(search_key, range, [search_type])

  • search_key is the record or value you want to find
  • range refers to the column or row you want the function to search
  • search_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";C2:C24;0)match_ss1.png

Here are the formula values broken down:

  • range = C2:C24
  • search_type = 0
  • search_key = “Davis”match_ss2.png

2. The formula result is “13”, which refers to the position value of our search_key “Davis”. (the 13th row in the column range we specified) 🎉match_ss3.png

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))

  • reference is the range of cells from which the values are returned.
  • search_key is the record or value you want to find
  • range refers to the column or row you want the function to search
  • search_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 SF 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))indexmatch_ss1.png

Here are the formula values broken down:

  • reference = A3:A35
  • search_key = E11
  • range = E3:E35
  • search_type = 0image (13).png

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. 👍🏼indexmatch_ss3.png

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 put this simply - enter the formula in the first cell, define the size of the Array and the function will apply to each cell within the range. The Array formula function outputs the values from cell ranges into multiple columns and rows.

Format: =ARRAYFORMULA(array_formula)

Example: We’ll use INDEX and ARRAY FORMULA functions from the “All SF Opportunities import” and capture all the information for the Account Name = “Edge Installation” and return the values of the entire row of cells (Column A-E).

1. On cell F3, add the ARRAYFORMULA we’ll use to capture the entire values of row 26.

=ARRAYFORMULA(INDEX(A3:E35;26;0)array_formulass1.png

Here are the formula values broken down:

array_formula = INDEX(A3:E35;26;0)array_formulass2.png

2. The results show all the values on a new row containing all the information for “United Oil SLA” from the designated range. 🎉array_formulass3.png

3. Another way to use the ARRAY FORMULA would be to populate all the values within the cells of a specified range. Formula used: =ARRAYFORMULA(INDEX(A3:A35;0;0))array_formulass4.png

Was this article helpful? 0 out of 0 found this helpful