Dynamic Find End of Year

[ryancgarrett]

I am trying to build a dynamic table that looks up inventory value at end of the year. Data is shown below:

	2014	2015	2016	2017		11/31/14	12/31/14	1/31/15	2/28/15	3/30/15	4/30/15	5/31/15	6/30/15	7/31/15	8/31/15	9/30/15	10/31/15	11/30/15	12/31/15
Inventory	100000	102000				90000	100000	93000	98000	115000	105000	110000	110000	85000	92000	97000	103000	101000	102000

<tbody>
</tbody>

The bolded values are the ones that should be calculated. The years above the bolded values are dynamic, based on a start date in an assumptions page, as are the dates in the 2nd table.

I need to write a formula that takes the year above and reports the value in the second table for 12/31 of that year.

I've played around a bit with Index-Match, but unsuccessfully. If someone could at least start me off in the right direction I would really appreciate it.

Thanks!

 

[ryancgarrett]

Sorry guys, writing it down helped me solve it myself. I used an hlookup as follows:

=HLOOKUP(DATE(B1,12,31),G1:T2,2,0)

Let me know if there is a more efficient way than that.

Thanks again!