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

[Jeffrey Green]

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

 

[FDibbins]

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
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]Sheet1[/td][td][/td][td][/td][td][/td][td]Sheet2[/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]SKU[/td][td]Date[/td][td]Price[/td][td][/td][td]SKU[/td][td]PRICE[/td][td]DATE[/td][/tr]

[tr][td]

3​

[/td][td]

123​

[/td][td]

6/21/2018​

[/td][td]

50​

[/td][td][/td][td]

123​

[/td][td]

10​

[/td][td]

6/1/2018​

[/td][/tr]

[tr][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td]

234​

[/td][td]

20​

[/td][td]

6/6/2018​

[/td][/tr]

[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td]

345​

[/td][td]

30​

[/td][td]

6/11/2018​

[/td][/tr]

[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td]

456​

[/td][td]

40​

[/td][td]

6/16/2018​

[/td][/tr]

[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td]

123​

[/td][td]

50​

[/td][td]

6/21/2018​

[/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td]

234​

[/td][td]

60​

[/td][td]

6/26/2018​

[/td][/tr]

[tr][td]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td]

345​

[/td][td]

70​

[/td][td]

7/1/2018​

[/td][/tr]

[tr][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td]

456​

[/td][td]

80​

[/td][td]

7/6/2018​

[/td][/tr]
[/table]
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))