Gift Register for a Function in Excel: Cash Gifts, Items and What We Gave Them Earlier

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: gift-register-function-template.xlsx: example data with real formulas. Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and everything recalculates.

Made-up data: every name, amount, shop and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The example

Gifts at the function (made-up values):

Cash received: ₹12,803 from 6 guests (average ₹2,134). Items worth ₹2,300. Relatives gave ₹10,201 in all. 3 guests gave more than the Bagales gave them earlier, and 2 are not in the earlier list yet.

2. Build it step by step

Step 1. The register

1. In A4:H4 type Guest, Side, Gift type, Amount or value, Note, (blank), We gave them earlier, Difference.
2. Drop-downs: B5:B200 Relatives,Friends,Neighbours,Work and C5:C200 Cash,Item (drop-down lists).

Step 2. What we gave earlier

1. In K4:L4 type What we gave at their function, Amount, and list names and amounts in rows 5 to 10.

Step 3. Look it up

1. In G5 type =IFERROR(INDEX($L$5:$L$10,MATCH(A5,$K$5:$K$10,0)),"Not in list").
2. MATCH finds the guest's row in K, INDEX returns the amount from L, and IFERROR shows "Not in list" instead of #N/A (INDEX and MATCH, IFERROR).

Step 4. Difference

1. In H5 type =IF(ISNUMBER(G5),D5-G5,""). ISNUMBER skips the "Not in list" rows. Copy G5:H5 down.

Step 5. Totals

1. In D15 type =SUMIF(C5:C12,"Cash",D5:D12): ₹12,803, and in D16 the same for "Item".
2. In D17 type =AVERAGEIF(C5:C12,"Cash",D5:D12): ₹2,134 (AVERAGEIF).
3. In D18 type =SUMIF($B$5:$B$12,"Relatives",$D$5:$D$12) and repeat for each side. In D23 type =COUNTIF(H5:H12,">0").

Step 6. Colours

1. Select H5:H12 > Highlight Cells Rules > Greater Than 0 green and Less Than 0 amber (highlight rules).

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in the file.

4. Common mistakes

1. Different spellings in the two lists. "Bagale cousins" and "Bagale Cousins " may not match. Copy names from one list to the other.
2. Leaving #N/A on the sheet. Wrap the lookup in IFERROR so new guests show "Not in list".
3. Typing item gifts as 0. Type an estimated value, or the totals and averages by side will be off.
4. Subtracting text. D5 − "Not in list" gives #VALUE!. ISNUMBER checks first.
5. Not updating the earlier list. After you attend someone's function, add what you gave to K:L.

5. More real examples

Example 1: Wedding in the home. Add a "Function" column so one register covers haldi, wedding and reception.

Example 2: Office farewell collection. Guests become colleagues, and the earlier list becomes what they gave at past farewells.

Example 3: Planning your next gift. Before attending Raja's function, the register shows he gave ₹2,100, so you can plan your gift.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I keep a gift register in Excel?

One row per guest with side, gift type and amount; SUMIF totals by side and type.

How do I look up what I gave a guest earlier?

Keep a second list and use =IFERROR(INDEX(amounts,MATCH(name,names,0)),"Not in list").

Why IFERROR?

MATCH returns #N/A when a name is missing; IFERROR shows a friendly message instead.

Can I use XLOOKUP instead?

Yes, in Microsoft 365 and Excel 2021: =XLOOKUP(A5,$K$5:$K$10,$L$5:$L$10,"Not in list") does the same.

How do I average only cash gifts?

=AVERAGEIF(C5:C12,"Cash",D5:D12).

7. Quick quiz

Ten questions on this page. Sign in to save your score.