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.

 

[dermie_72]

Create a new column called New Booked out and adde this formula into the second row.

=M2+MINIFS(E$2:E2,B$2:B2,B2)

Copy that down and it wont consider lower changes just the minimums above.
If this is not what you're after, please provide some data showing the start and what you expect the result to be.

 

[ronie85]

Hi and thanks for that. It works to an extent, 90% of what I am looking. However MINIFS seems to give me the smallest value in the range, and changing to MAXIFS gives me the highest value in the range. What I require is the last shown value in the range therefore if the price increases or decreases, it will pick up the most recent value.

My new Column is M which shows a 0 if no stock booked out, or a 1 if stock is booked out. In cell N530 the formula currently is =M531*MAXIFS(E$2:E531,B$2:B531,B531) to give the maximum unit price if something has been booked out.
If the MAX or MIN can be replaced to find the last entry in the range that would be great.

 

[kvsrinivasamurthy]

This formula is from 2 to 12 rows. Change as required.

M1= part number for which value required.
In M2

[U]=MAP(B2:B12,E2:E12,LAMBDA(a,b,IFERROR(INDEX($E$1:b,MAX(IF($B$2:a=$M$1,ROW($E$2:b),""))),"")))[/U]

 

[ronie85]

This formula is from 2 to 12 rows. Change as required.

M1= part number for which value required.
In M2

[U]=MAP(B2:B12,E2:E12,LAMBDA(a,b,IFERROR(INDEX($E$1:b,MAX(IF($B$2:a=$M$1,ROW($E$2:b),""))),"")))[/U]

Thanks though I can't seem to get it to work as it shows #CALC!

 

[kvsrinivasamurthy]

Formula is

=MAP(B2:B12,E2:E12,LAMBDA(a,b,IFERROR(INDEX($E$1:b,MAX(IF($B$2:a=$M$1,ROW($E$2:b),""))),"")))

 

[ronie85]

Are you using the formula in vba code. This formula is not for vba. It's for Excel sheet.

Yeah this is for a standard Excel Sheet.

M1 is a header "Price Out"
M2 through to M999 will be the line price based on the Item Part number shown within column B of the same line.
E2 through to E999 will be the Buy Price of the item.
If an item is booked out in the red section of column I89 for example, the formula will need to check the part number in B89 and match that part number to B2-B89 along with the Price in E2-E89 and return the value of the most southern price input of that item. Not the minimum or maximum value, just the most recent which would be the most recent input up until row 89.

 

[kvsrinivasamurthy]

There was a mistake in posting the formula. I have posted the formula in post #6. Pl check once again.

 

[ronie85]

There was a mistake in posting the formula. I have posted the formula in post #6. Pl check once again.

It is still giving me the same error

 

[kvsrinivasamurthy]

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