1) You will get back your missing value if you change all the ranges in the formula to start on row 1 instead of row 2. Trust me.

2) You can eliminate the array formula if you add a "helper" column to the records to INDEX the current value you're displaying, then a Simple INDEX/MATCH can bring over all the matching rows.

3) To write a code (which I would certainly do in this scenario), I'd need a sample workbook dumbed down to about 100 rows of Records and a CalCulation sheet displaying an example of the results you'd want, be sure to point out the INPUT cell where you will make your selection each time, if it's not patently obvious.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.


I can show you the formula technique, too, very robust and no VBA needed, though the sheet will get a little larger, no biggie.