Show most recent value within column (furthest row down)

[ronie85]

Hi, I have built a stock list for which part numbers are in Column B (ITEM CODE) and purchase price is in Column E (PURCHASE PRICE).
Values increase over time therefore I require when an item is booked out to search for the most recent (lowest) value within Column E (PURCHASE PRICE), and add this to that row within Column M (BOOKED OUT PRICE).
However if the value is to show in M500, it cannot use a value from 501 and up, as when a new price is included, it will change all previous booked out prices. (I suppose I can lock the starting cell and not the final cell)

Therefore Cell M500 has to search for a part number within B1 to B500 and show the value within the same row between E1 and E500.

 

[ronie85]

There is a confusion.
For I89 price is E89 as per your explanation.
Or do you mean most recent from E2 to E88.

I89 is just a quantity that is booked out, it doesnt need to form part of the required formula.

If a part number is inserted into B89 but B89 does not have a buy price in E89, but the part number also appears in B3, B18 and B30 for which each of them 3 do have buy prices, the formula is to search B2 to B89 for the part number for which it will appear 4 times in B3, B18, B30 and B89, and search E2 to E89 for the most recent buy price which will be in E30 as it exceeds E3 and E18, but because E89 doesnt contain a price.

 

[Peter_SSs]

If a part number is inserted into B89 but B89 does not have a buy price in E89, but the part number also appears in B3, B18 and B30 for which each of them 3 do have buy prices, the formula is to search B2 to B89 for the part number for which it will appear 4 times in B3, B18, B30 and B89, and search E2 to E89 for the most recent buy price which will be in E30 as it exceeds E3 and E18, but because E89 doesnt contain a price.

Does this do what you want?

24 11 02.xlsm
	B			E									N
1	Item Code
2	Code 9			1									1
3	Code 10			7									7
4	Code 8												N/A
5	Code 10												7
6	Code 10												7
7	Code 10			5									5
8	Code 2			7									7
9	Code 9												1
10	Code 4												N/A
11	Code 1			6									6
12	Code 10												5
13	Code 5			3									3
14	Code 5												3
15	Code 10												5
16	Code 4			2									2
17	Code 5												3
18	Code 1												6
19	Code 7			3									3
20	Code 4			55									55
21	Code 10												5
22	Code 7												3
23	Code 10												5
24	Code 7			4									4
25	Code 3												N/A
26	Code 2												7
27	Code 2												7
28	Code 1												6
29	Code 7			6									6
30	Code 7												6
ronie85

Cell Formulas
Range	Formula
N2:N30	N2	=TAKE(FILTER(E$1:E2,(B$1:B2=B2)*(E$1:E2>0),"N/A"),-1)

If this is not what you want, could you post a smallish but representative set of sample data with XL2BB after hiding all the irrelevant columns.
Could you then fill in the expected results manually and post the sheet again with XL2BB and explain a few of the results with specific reference to the sample data?

(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[kvsrinivasamurthy]

In M2

=MAP(B2:B999,E2:E999,I2:I999,LAMBDA(a,b,c,IF(c="","",IF(b<>"",b,IFERROR(INDEX($E$2:b,LARGE(IF($B$2:a=a,ROW($E$2:b)-1,""),2)),"")))))

 

[ronie85]

That works great thanks a lot

 

[Peter_SSs]

That works great thanks a lot

It would be good for helpers to know what works great as you have been given two quite different suggestions & they cannot both work.

 

[ronie85]

It would be good for helpers to know what works great as you have been given two quite different suggestions & they cannot both work.

The =TAKE(FILTER(F$1:F774,(B$1:B774=B774)*(F$1:F774>0),"N/A"),-1) formula works great

 

[Peter_SSs]

Thanks for letting us know.