Vlookup with 2 criteria help

Hi,

Try

Formula:

Please Login or Register  to view this content.

Incidentally most of us avoid merged cells like the plague, they just cause far too much trouble in all sorts of ways.
It's not so important here since column A isn't being used in any calculation but be aware. Ideally you would have the year number in every row in column A although it seems somewhat redundant since you have actual dates.

Also, I've used Vlookup and where you insert the column number, you use MATCH.

Match works similarly to vlookup, MATCH(lookup value, lookup row), except it returns a column number in the row, not the value.


I personally use one that is =VLOOKUP($A2, 'usage!' $A$2:$NV$290, MATCH(B$1,'usage!' $B$2:$NV$1)+1,FALSE)

since Match returns the column number, and my Match starts in B instead of A, that '+1' at the end helps with alignment to get the correct data.

Hi,

The row number of each row is the same as your consecutively numbered monthly periods plus 2

Hence all you need to do is identify the row number. Since there are always 12 periods in a year multiplying the number of years by 12 will give you a starting number. e.g. multiplying M4 x 12 when M4 = 1 gives 12. Now we deduct 10 to give us a start row for the year, i.e. row 2 and then add in the period number in the year (M5) and that gives us 14. Hence 14 is the index number for the row element of the INDEX() function.