Lookup assistance needed please

[formulacrazy]

So this is my data layout:

Sheet1

Row 3 are month names in the format of MMM.
Columns C to N contain various values.

Sheet 2 is an invoice template, which currently needs to be manually updated each month.

What i am trying to do is a Hlookup, looking at the invoice date and picking up the value for the pevious month on sheet on row 34.

I have tried =HLOOKUP("DEC",'sheet1'!C3:N36,34,False), and this works, however......what im trying to do is get the "DEC" bit to show the month before the invoice date automatically so it doesnt have to be manually updated each month (there are alot of these templates ). The invoice date in sheet 2 is in H5 of sheet 2 and is calcuated with=Today().

Appreciate HLookup may not be the best way to go, so a huge gold star to anyone who can help me, pretty please!

 

[Alex Blakenburg]

Could you please update your profile to show what version of Excel you are using ? If you have MS365 or O2021 there will be more options.

The below should work for you.
Ideally you would also use a lookup to determine the row number rather than hard coding the 34 but we have no visibility of your data.

=HLOOKUP(TEXT(EOMONTH(H5,-1),"mmm"),Sheet1!$C$3:$N$36,34,FALSE)