Formula for comparing rows for matches in multiple columns

Question

I am creating a net-worth calculator in Excel. I am planning to enter the bank and equity details once a week. I want to know the change in my portfolio compared to the last entry using a function. I can write a VBA code to search for the previous value for a particular category but I want to know if XLookup or something else can help me.

I want a formula for column Diff. I tried the below formula but it gives me the first or the current matching row and I need the previous one.

=XLOOKUP(E42;E:E;I:I;;;-1)

Your problem is the entire column references E:E and I:I. You want a mixed reference like E$2:E2 that expands as you drag down. — BigBen
– BigBen, Commented Aug 7, 2024 at 14:04
Alternatively, can spill it with dynamic array formula: =MAP(B2:B7,E2:E7,LAMBDA(α,δ, IFERROR(δ-XLOOKUP(α,DROP(B2:α,-1),DROP(E2:δ,-1),,,-1),0))) — Mayukh Bhattacharya
– Mayukh Bhattacharya, Commented Aug 7, 2024 at 15:07

Darren Bartrup-Cook · Accepted Answer · 2024-08-07 14:09:21Z

1

As @BigBen said - you need a combination of absolute and relative referencing:
=IFERROR(E2-XLOOKUP(B2;$B$1:B1;$E$1:E1;;;-1);"") < add this to cell F2 in the below image and drag down.

answered Aug 7, 2024 at 14:09

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Formula for comparing rows for matches in multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related