Profit Hawk automatically updates your Amazon Seller data into a dedicated data sheet within Google Sheets. To create your own custom reports and dashboards, you’ll use VLOOKUP formulas to fetch data from this data sheet. Here’s how you can start simply and gradually create more powerful, flexible formulas.
Level 1: Basic VLOOKUP
In the simplest scenario, you already know exactly which column you want data from, and you know its position (column number).
Example Formula:
=VLOOKUP("SSR-64-CM3", 'Profit Hawk Raw US Data Sheet'!$A:$ZZ, 12, FALSE)What this does:
Looks up the SKU “SSR-64-CM3”.
Searches the sheet named Profit Hawk Raw US Data Sheet.
Returns the data from column 12.
When to use:
Ideal when column structure doesn’t change and you know the exact column number.
Level 2: Intermediate VLOOKUP with Dynamic Column Lookup
This next example allows you to dynamically look up a column by name, making your formula more flexible if the column positions change.
Example Formula:
=VLOOKUP(
"SSR-64-CM3",
'Profit Hawk Raw US Data Sheet'!$A:$ZZ,
MATCH(
"SP Spend (Last 7 Days)",
ARRAYFORMULA(SUBSTITUTE('Profit Hawk Raw US Data Sheet'!$1:$1, CHAR(10), " ")),
0
),
FALSE
)
What this does:
Looks up the SKU “SSR-64-CM3”.
Searches for the column named “SP Spend (Last 7 Days)”.
The
SUBSTITUTEfunction removes line breaks from headers to ensure accurate matching.
When to use:
Perfect for sheets where columns might shift positions.
Allows users to use column names instead of fixed column numbers.
Level 3: Advanced VLOOKUP with Fully Dynamic References
In this advanced scenario, your SKU and data sheet name are dynamic. This allows maximum flexibility when generating reports or changing SKUs.
Example Formula:
=VLOOKUP(
"SSR-64-CM3",
'Profit Hawk Raw US Data Sheet'!$A:$ZZ,
MATCH(
"SP Spend (Last 7 Days)",
ARRAYFORMULA(SUBSTITUTE('Profit Hawk Raw US Data Sheet'!$1:$1, CHAR(10), " ")),
0
),
FALSE
)
What this does:
Dynamically looks up the SKU from cell B7.
Dynamically selects the data sheet using the name from cell W7.
Searches for the column named “SP Spend (Last 7 Days)” dynamically.
Why this is powerful:
Easily switch SKUs or data sheets by simply updating cells B7 and W7.
Great for building reusable, highly adaptable report templates.
Explanation: The SUBSTITUTE Function
You might notice the use of SUBSTITUTE. Profit Hawk headers often contain line breaks for readability. However, formulas require exact matches, so the SUBSTITUTE function removes these line breaks for accurate column matching:
SUBSTITUTE(range, CHAR(10), " ")
This ensures your formulas always find the right columns, even when visual formatting includes line breaks.
Becoming a Profit Hawk Reporting Expert
Understanding these three levels of VLOOKUP formulas allows you to build powerful and flexible reports tailored specifically to your Amazon Seller data needs. Start simple, master each level, and soon you’ll create sophisticated, custom Profit Hawk reports effortlessly!
Next Steps for Power Users
Swap
"SP Spend (Last 7 Days)"with any other header text to pull a different metric.Turn B7 & W7 into data‑validation dropdowns for a polished self‑serve dashboard.
Wrap formulas in
ARRAYFORMULAto bulk‑return multiple SKUs at once.Combine with
IMPORTRANGEif your report lives outside the Google Workspace Add‑on‑installed file.
You now have everything you need to craft bespoke reports and unlock the full value of Profit Hawk data—happy analyzing!
Troubleshooting
Symptom | Likely cause | Fix |
| SKU / ASIN not found | Verify the key in B7 exactly matches the raw data. |
| Sheet name wrong in W7 | Use the full sheet tab name—including spaces & punctuation. |
Header not matched | Hidden line breaks | Keep the |
Slow sheet | Many volatile | Replace some formulas with static copies or use the Profit Hawk → “Insert Values” command to paste results only. |