is =xlookup the formula I need?

[Mel Smith]

I run a weekly report that consists of a variable number of lines (500 - 1000+ lines). I have a master list of suppliers and their payment terms and I would like to automate against each line when it will fall due for payment, based on the delivery date and payment terms in my master list. Is using =xlookup the way i should go?

On my report sheet column the delivery date is in column C and supplier name in column F. I would put my formula in to column O.

Many thanks.

Mel

 

[Peter_SSs]

Sorry, I just feel like I'm missing a critical piece here.

That may be so, but you are also missing the correct syntax for XLOOKUP which is

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Your formula in column O will look something like this:

=XLOOKUP(F2,MasterList!$A$2:$B$14,2,FALSE, delivery_date)

Your formula appears to be a mixture of XLOOKUP and VLOOKUP syntax.

 

[mikenelena]

Yes, you can use the XLOOKUP function to automate this process. The formula will take the supplier name from Column F and lookup the payment terms from your master list. Then it will use the delivery date from column C to work out when the payment is due. Your formula in column O will look something like this:

=XLOOKUP(F2,MasterList!$A$2:$B$14,2,FALSE, delivery_date)

Where MasterList!$A$2:$B$14 is your master list and delivery_date is the delivery date in column C.

 

[Mel Smith]

Hi mikenelena,

I've put in your formula: =XLOOKUP(F2,Sheet2!$A$2:$B$42,2,FALSE, C2) and I'm getting a #VALUE! error.

Have I mis-copied something?

Mel

 

[mikenelena]

Can you give me 1 row of data to test with, if it isn't anything sensitive?

 

[Mel Smith]

Does this help?

PO-00001202

10/Nov/2022

25-Nov-22​

28/Nov/2022

Glebe (ATUK)

A1 Screws

A+DSCREW

Screw, self tapper, captive washer, 6.3mm x 3/4", black

Mel

 

[Mel Smith]

Hi Mikenelena,

I can understand the first part of your formula but how does the payment terms (column B of sheet 2) get added to the delivery date, (column C of sheet 1), to give me the Due Date for payment?

Mel

 

[mikenelena]

I think we are missing a few columns of data. I'm assuming Glebe (ATUK) is the supplier. Is 25 Nov 2022 the delivery date, and 28 Nov 2022 the payment terms? Sorry, I just feel like I'm missing a critical piece here.

 

[Mel Smith]

Hi Mikenelena,

The Delivery date is 25 Nov 22
The Supplier is A1 Screws
Payment terms are in column B of Sheet 2

Mel

 

[Alex Blakenburg]

You haven't given us visibility of your Terms table.
If it simply has a number in it such as 7, 14, 30 something like the below should work.

Book1
	A	B	C	D	E	F	G	H							O
1	Doc No	Col2	Delivery Date	Col3	Col4	Supplier	Col5	Description							Due Date
2	PO-00001202 10/	10-Nov-22	25-Nov-22	28-Nov-22	Glebe (ATUK)	A1 Screws	A+DSCREW	Screw, self tapper, captive washer, 6.3mm x 3/4", black							25-Dec-22
Sheet1

Cell Formulas
Range	Formula
O2	O2	=IFERROR(C2+XLOOKUP(F2,Sheet2!$A$2:$A$20,Sheet2!$B$2:$B$20,""),"Not Found or not a number")