Elizabeth has attended:
Excel Advanced course
Excel Intermediate course
XLOOKUP
I am struggling with using XLOOKUP.
I have a list of grant balance b/f with references in Column A and balances in Column B.
I then have a payments sheet with grant references (in column A) with payments made in the period for these grants (in Column B). Not all grants will have had a payment in the period.
My formula looks like this:
XLOOKUP(A2,RPG_Payments!A3:A392,RPG_Payments!B3:B159) and I have then tried to copy that down in Column C.
Ideally I would like it to return nothing or zero if there is no payment.
RE: XLOOKUP
Hi Elizabeth,
Thank you for the forum question.
The structure of the XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
The lookup_array must have the same number of rows as the return_array.
In your XLOOKUP: XLOOKUP(A2,RPG_Payments!A3:A392,RPG_Payments!B3:B159)
The number of rows are different.
In the [if_not_found] argument you can tell the function what to display if no payments, and in the [match_mode] argument you will have to enter 0 to tell the function that you only want exact match.
Try
XLOOKUP(A2,RPG_Payments!A3:A392,RPG_Payments!B3:B392,0,0)
This will return zero if no payments or
XLOOKUP(A2,RPG_Payments!A3:A392,RPG_Payments!B3:B392,"",0)
If you want a blank if no payments
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: XLOOKUP
Thanks, Jens.
A follow up question.
I discovered that while not all grants had any payments in the period, several had more than one.
If next time, I wanted Excel to add up the 2 or 3 payments that related to the same grant reference, how would I do that?
Do I need to use something else or can XLOOKUP handle this.
This is now low priority but would be useful for next time I run this as this time I manually adjusted for those with more than one payment.
Thanks,
Elizabeth
RE: XLOOKUP
Hi Elizabeth,
Try
=sumifs(RPG_Payments!$B$3:$B$392,RPG_Payments!$A$3:$A$392,A2)
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
