VLOOKUP specific value out of several

[Krotishka1]

I need some help with the VLOOKUP function. Say, there is a spreadsheet with history of production material supply/consumption.

Each row indicates the date, the type of material, supplied/consumed quantity and total quantity after the supply/consumption. The problem I have is with the total quantity column. I need to add the change in the quantity to the most recent value. At first I tried using VLOOKUP with Type of Material used as a lookup_value. Obviously, this did not work, as there are several values, and the function returned the first one out of them, while I need the last.

Then I tried combining IF, IFNA and VLOOKUP functions. First VLOOKUP checks the row above, and if there is no value (N/A), then the range for VLOOKUP increases. However, there is still a problem with this. The checked ranged is restricted by the amount of times I manually use IF/VLOOKUP combination, but the amount of rows (supply/consumption dates) increases infinitely with time. Is it possible to somehow make VLOOKUP range increase by 1 with each new row?

Or are there any other ways to solve the problem? Perhaps a way for VLOOKUP to return the last value instead of the first?

Thanks a lot in advance!

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[SjMaxwell]

I think a Sumif would work. See attached workbook. Basically this adds all the supplied/consumed amounts together each time a new one is entered instead of looking at the last remaining qty.

[AliGW]

SJ Maxwell - just as it would be good to see the OP's workbook, so it would be helpful of you to attach your sample solution as a workbook as well as outlining it in the thread. Thanks.

[SjMaxwell]

Will do, sorry about that.