IF and VLOOKUP with Dates values

[StaceyVECL]

Hi All,

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Date

Sku

Code

Qty

Po

Order#

Name

Ship Date

Tracking

Courier

Inv#

Cost

Shipping

Vat

Total Inc. Vat

Status

=IF(OR($B4="",$D4=""),"",IF(B4="","",(VLOOKUP(@$B$4:$B$500,Formulas!$A$1:$F$26,4,FALSE)))*D4)

I am using the above formula in column L and M to pull in a cost price for an item so that we know what to invoice. However, there is a new step. Cost price will change dependent on the date of the order for sales. Is there a way to use the formula as is but add in a step to use the date in Column A depending on the date on the formulas tab in F2 and F3 as per the below?

1	A	B	C	D	E	F	G
2				1/1/24	1/1/24	26/11/24	26/11/24
3				25/11/24	25/11/24	2/12/24	2/12/24
4	SKU	Description	Code	Cost	Shipping	Cost price sale	Shipping Sale

Thanks
Stacey

 

[Rijnsent]

Hi Stacey, quick & dirty, you could do it like this:

=IF(OR($B4="",$D4=""),"",IF(B4="","",(VLOOKUP(@$B$4:$B$500,Formulas!$A$1:$G$26,IF(A4>=DATE(2024,11,26),6,4),FALSE)))*D4)

Or you could use the new XLOOKUP function instead of VLOOKUP, but the concept of nesting an IF formula inside of it remains the same. If your number of options increases, you're probably better off with an INDEX MATCH combination formula.

Cheers,
Koen

 

[StaceyVECL]

Hi Stacey, quick & dirty, you could do it like this:

=IF(OR($B4="",$D4=""),"",IF(B4="","",(VLOOKUP(@$B$4:$B$500,Formulas!$A$1:$G$26,IF(A4>=DATE(2024,11,26),6,4),FALSE)))*D4)

Or you could use the new XLOOKUP function instead of VLOOKUP, but the concept of nesting an IF formula inside of it remains the same. If your number of options increases, you're probably better off with an INDEX MATCH combination formula.

Cheers,
Koen

Hi Koen,

The date range would need to be between 2 dates that fluctuate - for example between 26/11 to 3/12 that would change.

 

[Rijnsent]

Easiest option: in your example you have only 2 options: 1-1-2024 to 25-11-2024 and 26-11-2024 to 2-12-2024, but I guess you can have more columns to the right for other timeframes? Assuming that they are following each other (so last day of period 1 ends the day before period 2 starts):

=MATCH(A2,Formulas!$A$2:$G$2,1)

That formula will return the first column where the date is equal to or smaller than A2 - you can test this formula per row before you integrate it into your bigger formula. You can use that as third element in your VLOOKUP function.

=IF(OR($B4="",$D4=""),"",IF(B4="","",(VLOOKUP(@$B$4:$B$500,Formulas!$A$1:$G$26,MATCH(@$A$4:$A$500,Formulas!$A$2:$G$2,1),FALSE)))*D4)
'And for the other one
=IF(OR($B4="",$D4=""),"",IF(B4="","",(VLOOKUP(@$B$4:$B$500,Formulas!$A$1:$G$26,MATCH(@$A$4:$A$500,Formulas!$A$2:$G$2,1)-1,FALSE)))*D4)

 

[StaceyVECL]

Easiest option: in your example you have only 2 options: 1-1-2024 to 25-11-2024 and 26-11-2024 to 2-12-2024, but I guess you can have more columns to the right for other timeframes? Assuming that they are following each other (so last day of period 1 ends the day before period 2 starts):

=MATCH(A2,Formulas!$A$2:$G$2,1)

That formula will return the first column where the date is equal to or smaller than A2 - you can test this formula per row before you integrate it into your bigger formula. You can use that as third element in your VLOOKUP function.

=IF(OR($B4="",$D4=""),"",IF(B4="","",(VLOOKUP(@$B$4:$B$500,Formulas!$A$1:$G$26,MATCH(@$A$4:$A$500,Formulas!$A$2:$G$2,1),FALSE)))*D4)
'And for the other one
=IF(OR($B4="",$D4=""),"",IF(B4="","",(VLOOKUP(@$B$4:$B$500,Formulas!$A$1:$G$26,MATCH(@$A$4:$A$500,Formulas!$A$2:$G$2,1)-1,FALSE)))*D4)

Thanks for this but it doesn't do what I need it to do.

Which I think is because I'm not explaining myself very well. Sorry.

The cost price will depends 3 criteria:
1. A4 = Date
2. B4 = item code
3. D4 = quantity purchased

If I set out the cost prices in a different tab as the below will this work better?

Then the column it's looking for the cost price in will never change nor will the start and end dates.

Thanks in advance.

 

[StaceyVECL]

Thanks for this but it doesn't do what I need it to do.

Which I think is because I'm not explaining myself very well. Sorry.

The cost price will depends 3 criteria:
1. A4 = Date
2. B4 = item code
3. D4 = quantity purchased

If I set out the cost prices in a different tab as the below will this work better?
View attachment 120067

Then the column it's looking for the cost price in will never change nor will the start and end dates.

Thanks in advance

I have managed to do this using INDEX and MATCH formula.

Thank you everyone one for your help.

 

[Peter_SSs]

Good to hear you got the solution.
If you would like to post the actual solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[StaceyVECL]

Good to hear you got the solution.
If you would like to post the actual solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

I found the solution here: Matching on multiple criteria with a date range lookup