Vlookup between 2 tables

[shophoney]

Hi,

I would like to find a way to look up what the discount was on an item within a table.

Sku 1100748, what was the price back in November 1/19

Below shows it has been marked down 3 times. the price from Aug 1/19 on was $20 (disc_amt).

How can I tell vloookup to look at the price that was in Nov 1/19. This item could have been further reduced after Nov 19, so it needs to look after / before dates.

thanks

 

[Alex Blakenburg]

My interpretation is that you want the last price equal to or before the transaction date for that SKU.
If that is correct then this should work for you.

Don't let my dates throw you, I am in Sydney Australia and our date format is dd/mm/yyyy

The MaxIfs finds the latest date that is <= the transaction date for that sku.
The SumIfs then uses that date and the sku to get the discount amount.

20210226 XLookup Approx Sort Test.xlsx
	A	B	C	D	E	F
1
2	date	sku_no	disc_amt lookup
3	1/11/2019	1100748	20
4
5
6	sku_no	disc_code	seq_no	disc_amt	disc_type	start_active_dt
7	1100748			20	O	1/08/2019
8	1100748			30	O	4/12/2018
9	1100748			30	O	22/11/2018
10
Price History

Cell Formulas
Range	Formula
C3	C3	=SUMIFS(db_DiscHistory[disc_amt], db_DiscHistory[sku_no],$B3, db_DiscHistory[start_active_dt],MAXIFS(db_DiscHistory[start_active_dt],db_DiscHistory[sku_no],B$3,db_DiscHistory[start_active_dt],"<="&$A3))

 

[shophoney]

My interpretation is that you want the last price equal to or before the transaction date for that SKU.
If that is correct then this should work for you.

Don't let my dates throw you, I am in Sydney Australia and our date format is dd/mm/yyyy

The MaxIfs finds the latest date that is <= the transaction date for that sku.
The SumIfs then uses that date and the sku to get the discount amount.

20210226 XLookup Approx Sort Test.xlsx
	A	B	C	D	E	F
1
2	date	sku_no	disc_amt lookup
3	1/11/2019	1100748	20
4
5
6	sku_no	disc_code	seq_no	disc_amt	disc_type	start_active_dt
7	1100748			20	O	1/08/2019
8	1100748			30	O	4/12/2018
9	1100748			30	O	22/11/2018
10
Price History

Cell Formulas
Range	Formula
C3	C3	=SUMIFS(db_DiscHistory[disc_amt], db_DiscHistory[sku_no],$B3, db_DiscHistory[start_active_dt],MAXIFS(db_DiscHistory[start_active_dt],db_DiscHistory[sku_no],B$3,db_DiscHistory[start_active_dt],"<="&$A3))

Hi Alex,

Your solution is great.

I just have one problem. I want to apply the same formula for the next column.

I need to pull the first four characters. So 30PC200107 = 30pc or $20N071818 = $20n

But the formula you provided seems to pull 0

Steven

 

[Alex Blakenburg]

Hello Steven,
Sumifs works in more versions of Excel but only works if the return value is numeric.
Here is the same formula converted to an XLookup in the hope your version of Excel supports that.
I am assuming you are ok to wrap that with a Left("return_value",4)

=XLOOKUP(1,
(db_DiscHistory[sku_no]=$B3)*
(
db_DiscHistory[start_active_dt]=MAXIFS(db_DiscHistory[start_active_dt],db_DiscHistory[sku_no],B$3,db_DiscHistory[start_active_dt],"<="&$A3)),db_DiscHistory[disc_amt],"",0)

PS: its 3:30 am in the morning here (Sydney Aust) - so I hopefully I won't respond again until later today.

 

[Alex Blakenburg]

I hope my PS didn't put you off, how did you go with the Xlookup ?

 

[shophoney]

Alex,

Thanks that is great. I hate to keep asking questions.

But numbers can cause confusion.

As you will see below. when the discount is % I want the highest % off. And when it's $ i want the lowest price.

Using the formula you provided picked $78 on Nov 1/19 date. But price should have been $40 on 9/12/19 (September)

I hope my PS didn't put you off, how did you go with the Xlookup ?

Thanks for all your help Alex. I am currently testing the X look up function in XL but there is a lot of data for me to review. Thanks

 

[Alex Blakenburg]

Assuming your picture starts at Column A, send me a copy of the formula you are using and a picture or XL2BB shot of the sheet with the lookup in it. If a picture you need to include the Row & Column information.
Based on SKU 1134082 and the Date being Nov 1/19, the formula I sent should have picked up the 40.