The Scenario
It's the last working day of the month. The finance coordinator needs to match Referral Rock's pending reward payouts against the accounts-payable workbook before running the disbursement batch. She has 340 pending payouts in Referral Rock and an AP ledger in Excel.
She's asked IT twice about an automated sync. Both times, the answer was "backlog." So she exports the payout CSV, reformats it in Power Query, and cross-references it manually against the AP ledger using VLOOKUP. Every month.
The bad version:
- Export pending payouts from Referral Rock as a CSV — navigate to Reports, filter to "pending," download.
- Open in Excel, run Power Query to reorder columns to match the AP ledger schema.
- Run VLOOKUP to find payouts in Referral Rock that don't appear in AP, and vice versa.
The download has 340 rows. The AP ledger has 380. Fourteen of them don't match because the email format in Referral Rock includes a display name in angle brackets. The VLOOKUP fails on those fourteen silently.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Excel workbook. It reads the workbook, fetches the pending payouts from Referral Rock, and writes the result into the worksheet you specify — so you can run your VLOOKUP against clean, consistently formatted data.
Pull all pending Referral Rock payouts with recipient email, reward amount, and eligibility date into columns A through D of the Payouts worksheet, with headers in row 1
What You Get
- Row 1: Recipient Email, Reward Amount, Eligibility Date, Payout ID.
- Rows 2 through 341: one row per pending payout, amounts as numbers not strings.
- Emails arrive in plain format — no display names, no angle brackets — so your VLOOKUP against the AP ledger works without a workaround.
What If the Data Is Not Quite Ready
You also want redeemed payouts in the same pull so you can see the full picture
Import all Referral Rock reward payouts — both pending and redeemed — into the Payouts worksheet with recipient email, reward amount, payout status, and eligibility date in columns A through D
The AP ledger uses member IDs instead of emails as the join key
Fetch all pending Referral Rock payouts with member ID, recipient email, reward amount, and eligibility date into the Payouts worksheet, columns A through D, so I can join on member ID against the AP ledger
Reward amounts are in different currencies and you need them normalized to USD
Pull all pending Referral Rock payouts into the Payouts worksheet with recipient email, original reward amount, currency code, and eligibility date; convert any non-USD amounts to USD using the rate in cell B1 of the FX Rates worksheet and write the converted amount into column E
Pull payouts, match against AP ledger, and flag discrepancies in one shot
Fetch all pending Referral Rock payouts into the Payouts worksheet with recipient email and reward amount; then compare each row to the AP ledger in the AP worksheet (email in column A, expected amount in column B) and write "matched", "amount mismatch", or "not in AP" into column C of the Payouts worksheet
One prompt handles the pull and the reconciliation so you hand finance a workbook that's already annotated — not a raw export that still needs three formulas built on top of it.
Try It
Open your monthly accounts-payable reconciliation workbook and get the 7-day free trial of SheetXAI. Ask it to pull all pending Referral Rock payouts and match them against your AP ledger. Related reads: issuing manual bonus rewards and exporting all rewards data for liability reporting — both in the Referral Rock hub.