Show most recent value within column (furthest row down)

ronie85

Board Regular
Joined
Jan 25, 2014
Messages
96
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Create a new column called New Booked out and adde this formula into the second row.
Excel Formula:
=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.
 
Upvote 0
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.
 
Upvote 0
This formula is from 2 to 12 rows. Change as required.

M1= part number for which value required.
In M2
Excel Formula:
[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]
 
Upvote 0
This formula is from 2 to 12 rows. Change as required.

M1= part number for which value required.
In M2
Excel Formula:
[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!
 
Upvote 0
Formula is
Excel Formula:
=MAP(B2:B12,E2:E12,LAMBDA(a,b,IFERROR(INDEX($E$1:b,MAX(IF($B$2:a=$M$1,ROW($E$2:b),""))),"")))
 
Last edited:
Upvote 0
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.

1730477114221.png
 
Upvote 0

Forum statistics

Threads
1,223,880
Messages
6,175,154
Members
452,615
Latest member
bogeys2birdies

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