Find SKU price from a history of prices if i know the sales date

Jeffrey Green

Well-known Member
Joined
Oct 24, 2007
Messages
1,021
i have a list of SKU's on Sheet 1.
I have historical pricing on sheet 2 by SKU and price date.
I am trying to "vlookup or Index/Match, etc to bring in the historical price into sheet 1 that is immediately prior to sales date on Sheet 1

I can NEVER get this right . . .

thanks
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
If you want to bring back the SKU price based on the SKU date,you wont be able to use vlookup - the search criteria needs to be in the very 1st column of the range.
Because you need to test for 2 things - SKU and Date, try this
A​
B​
C​
D​
E​
F​
G​
1​
Sheet1Sheet2
2​
SKUDatePriceSKUPRICEDATE
3​
123​
6/21/2018​
50​
123​
10​
6/1/2018​
4​
234​
20​
6/6/2018​
5​
345​
30​
6/11/2018​
6​
456​
40​
6/16/2018​
7​
123​
50​
6/21/2018​
8​
234​
60​
6/26/2018​
9​
345​
70​
7/1/2018​
10​
456​
80​
7/6/2018​
B3 would contain your selected date
C3=INDEX($F$3:$F$10,MATCH(A3&" "&B3,INDEX($E$3:$E$10&" "&$G$3:$G$10,0),0))
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top