XLOOKUP

Resolved · Medium Priority · Version 365

Fri 14 Jul 2023, 11:59 Delegate Elizabeth said...

Elizabeth has attended:
Excel Intermediate course
Excel Advanced 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.

Fri 14 Jul 2023, 12:21 Trainer Jens said...

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

Fri 14 Jul 2023, 16:01 Delegate Elizabeth said...

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

Fri 14 Jul 2023, 16:12 Edited on Fri 14 Jul 2023, 16:13 Trainer Jens said...

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