Formula to get the last value in a data range

Michael Gibson

Is there a work around that will provide you with the last value in a data range instead of the first as vlookup would do? Also, we would need to ignore any cells that were blank.

MCorbin

OK, fixed it....

First... You need to create a Helper Column I called it "CurrContrMatch". The formula in that column will be:

=[Contract List]@row + " " + [Contract Mods]@row

THEN, your formula for the Current Contract Value would be:

=INDEX([Contract Value]:[Contract Value], MATCH(([Contract Number]@row + " " + MAX(COLLECT([Contract Mods]:[Contract Mods], [Contract List]:[Contract List], [Contract Number]@row))), CurrContrMatch:CurrContrMatch, 0))

You can hide the CurrContrMatch column once you create and test your formulas (recommend making it a Column Formula so it doesn't accidentally get broken).

David Joyeuse

Hi @Michael Gibson

Instead of VLOOKUP, try using MAX(COLLECT([Date to Collect Range}; {Date to collect range}; ISDATE(@cell)))

Should do the trick.

Michael Gibson

Hi @David Joyeuse ,

Thanks for your feedback. I'm having trouble getting that to work. I'll post an example of a situation where I'm looking to get the current value of a contract which has had 4 modifications. In the example the value went up, was unchanged and left blank and then went down.

Thanks Again.

MCorbin

Try this:

=INDEX([Contract Value]:[Contract Value], MATCH(MAX(COLLECT([Contract Mods]:[Contract Mods], [Contract List]:[Contract List], [Contract Number]@row)), [Contract Mods]:[Contract Mods], 0))

MCorbin

Oops - there's a problem with my formula, but I need to go into a meeting. I'll work on fixing it when my meeting is done (sorry!)

MCorbin

OK, fixed it....

First... You need to create a Helper Column I called it "CurrContrMatch". The formula in that column will be:

=[Contract List]@row + " " + [Contract Mods]@row

THEN, your formula for the Current Contract Value would be:

=INDEX([Contract Value]:[Contract Value], MATCH(([Contract Number]@row + " " + MAX(COLLECT([Contract Mods]:[Contract Mods], [Contract List]:[Contract List], [Contract Number]@row))), CurrContrMatch:CurrContrMatch, 0))

You can hide the CurrContrMatch column once you create and test your formulas (recommend making it a Column Formula so it doesn't accidentally get broken).

Michael Gibson

Thanks @MCorbin , that did the trick.