LOOKUP price based on date (but not necessarily the most recent date)

[Yamezz]

I would like a formula to return the price of an item from a list that includes historical pricing.
I have been using the following formula to return the item price:

=VLOOKUP(L10718,InputCosts,2,FALSE)

which works well when there is only one price listed for an item. I now need to update the price going forward with the new price, however I don't want to update the historical records to the new price.
I need to use the date in column A to return the correct price, with logic something along the lines of: Price = most recent price from list, before the date in column A.
For example, if the Item in column L is "Pads" and the value in column A is 7-May, the result should be $3.50, but if the date is 5-May, then the result should be $2.75

My "InputCosts" table is attached. I'd appreciate any help I can get.

 

[Peter_SSs]

Could we have some sample data that we can test without having to manually type it out? XL2BB
Is the formula going in a column of that table you have shown or elsewhere?

 

[Yamezz]

Could we have some sample data that we can test without having to manually type it out? XL2BB
Is the formula going in a column of that table you have shown or elsewhere?

I tried using XL2BB, but it froze Excel and required a CTRL+ALT+DEL to get out of...
The formula will go into column O of another sheet in the same workbook.

I'll see what I can do about the sample data.

 

[Yamezz]

This is the best I can do, sorry. Excel shows XL2BB is installed, but it's not showing up on the menu bar.

Item	Cost	Date
Pads	$2.750​	10/04/22​
H2T	$0.009​	11/04/22​
SO2	$0.100​	01/11/21​
KHT	$0.011​	13/04/22​
Bento	$0.004​	14/04/22​
PMS	$0.006​	15/04/22​
PVPP	$0.042​	16/04/22​
Pads	$3.500​	6/05/22​

 

[Peter_SSs]

Excel shows XL2BB is installed, but it's not showing up on the menu bar.

Towards the bottom of the XL2BB link that I gave you is this section. Could the marked item be your problem in this regard?

This is the best I can do, sorry.

Thanks, that form of sample data is okay as we can at least copy it from there.
However, you didn't answer my other question.

Is the formula going in a column of that table you have shown or elsewhere?

 

[Yamezz]

The formula will go into column O of another sheet in the same workbook.

I'll read through the whole thread you referenced and see if I can get XL2BB working.

 

[Peter_SSs]

See if this is any use

Yamezz.xlsm
	C	D	E	F	G
1
2		Item	Cost	Date
3		Pads	$2.75	4/10/2022
4		H2T	$0.01	4/11/2022
5		SO2	$0.10	11/01/2021
6		KHT	$0.01	13/04/22
7		Bento	$0.00	14/04/22
8		PMS	$0.01	15/04/22
9		PVPP	$0.04	16/04/22
10		Pads	$3.50	5/06/2022
11
12
Sheet1

Yamezz.xlsm
	A											L			O
1	Date											Item			Price
2	25/07/2022											Pads			3.5
Sheet2

Cell Formulas
Range	Formula
O2	O2	=INDEX(Table1[Cost],AGGREGATE(15,6,(ROW(Table1[Cost])-ROW(Table1[#Headers]))/((Table1[Item]=L2)*(Table1[Date]=AGGREGATE(14,6,Table1[Date]/((Table1[Item]=L2)*(Table1[Date]<=A2)),1))),1))

 

[Yamezz]

Peter, I'm getting a #NUM! error. I did not have my table headers labelled, so have gone ahead and labelled them as per your formula, but I'm still ending up with an error.
I have tried pasting into a new workbook with only the data you supplied and defined names for Table 1, Item, Cost and Date. I've also tried pasting into my original workbook and changed the instances of "Table1" to "InputCosts" (the name of my table) in the formula.

 

[Peter_SSs]

I did not have my table headers labelled,

You did in post #1 and post #4

I have tried pasting into a new workbook .... and defined names for Table 1, Item, Cost and Date.

There are no defined names to be manually set up. My suggestion was based on the data shown in post #1 being in a formal Excel table (set up through Insert ribbon tab - Table or Ctrl+T). The only manual intervention in relation to names would be if you edited the formal table name in the Name Manager, which it sounds like you have done.
The other 'names' in my formula must match the relevant column headings in the formal table.

 

[Yamezz]

You did in post #1 and post #4


There are no defined names to be manually set up. My suggestion was based on the data shown in post #1 being in a formal Excel table (set up through Insert ribbon tab - Table or Ctrl+T). The only manual intervention in relation to names would be if you edited the formal table name in the Name Manager, which it sounds like you have done.
The other 'names' in my formula must match the relevant column headings in the formal table.

You're right, I did change create the table by selecting cells and using the Insert Table command, then I changed the table name to InputCosts.
Previously I didn't specifically name the headers (I didn't even realise you could do that initially), but when I saw I had an error and that your formula used terms like "Table1[Cost]", I thought that may be the cause of my error.

Unfortunately, whether I leave the defined header names there or delete them again, I get the error, and whether I use the named table, InputCosts and change the formula, or whether I use the formula as provided without naming the table, I still get the error.