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. 🚨

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 in the same sheet or from another sheet or workbook.

An important thing to note is that 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”).

   

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.

   

   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)