[SOLVED] Retreiving data from different column every month

[ballie]

Hi everyone,

I’ve created a worksheet in calc to manage my expenses. After trial and error I decided to add a matrix in my sheet with one column for each month, containing the amounts to be paid. I’m trying to get the amounts for the current month in the colunmn under “Amount”, but I just cant seem to get the formula right.

Can anyone tell me what I’m doing wrong here?

I’ve added a sample file with sample (fake) amounts. The salary dates are (in my case) once every four weeks) So that’s the reason that in january there are two paydates and I’ve entered a higher salary. Change the date to a week later, and that second date will appear in another month. The amount however is fake. Me real income is only for me to see

To be complete: My Native language is Dutch, and so is my edition of LibreOffice. I’m running LibreOffice under Kubuntu Linux version 18.04

Voorbeeld Maandlasten.xls

[anon73440385]

I do not understand what you want to achieve and do not get the idea behind the numbers in column “Maand” and why do you need in “Amount” numbers, which are already inr you matrix? The only thing my gut feeling tells me, that you have one serious problem, which is that you didn’t fix the range in your vlookup ( which should read Z$6:AL$24 - see the “$”) and second you might in fact searching for HLOOKUP the month in matrix and search down (instead of VLOOKUP and search to the right).

I assume you need something like this

=HLOOKUP(TEXT(TODAY();"MMM");AA$4:AL$18;ROW(A6)-3;0)

This to work requires you set the matrix headers to

=TEXT(D3;"MMM")

(example is for cell AA4). Hope that helps - please see my thoughts in Lasten.xls

[erAck]

Caveat, anything TEXT(…;“MMM”) depends on the current locale, month names change with different locales, so the calculation would fail.

[anon73440385]

Thanks and sure - but in fact the point is HLOOKUP instead of OPs efforts with VLOOKUP

[erAck]

Three things:

1. VLOOKUP() without a 4th argument (Sort order) or an argument not equal to 0 or FALSE expects the lookup range to be sorted, which it is not, the result then is arbitrary.
2. Copying down the VLOOKUP() formula in C6:C18 you made the mistake to not use absolute row references for the lookup range (and probably some other editing error as well, looks odd), resulting in shifted references like =VLOOKUP(Z8;Z7:AL26;MONTH(TODAY())) in C8.
3. VLOOKUP() looks up the given value in the first column of the given array/range, with your =VLOOKUP(Z6;Z6:AL24;MONTH(TODAY())) in C6 you always lookup Z6 in range Z6:Z24 and then return a value from another column. While this is ok (except the missing 4th argument 0 mentioned above) in this case, it is unnecessary. You want an offset of month within the matrix instead, so =OFFSET(Z6;0;MONTH(TODAY())) in C6 and copy that formula down.

Hope that helps.

[erAck]

Also, it probably would be better / easier to overview to use the titles like Insurance and Mortgage also in the planning matrix as first column under Maand instead of the lookup numbers, then a VLOOKUP() would even make sense again like =VLOOKUP(A6;Z$6:AL$18;MONTH(TODAY());0) in C6. Note the absolute $ row reference to be able to copy the formula down and 4th argument 0.

[ballie]

Thank you erack and Opaque. I guess I just got tunnelvision in trying to get VLOOKUP to work. Never even looked at the other one.

I’ve incorporated Opaque’s solution and it worked perfectly. So a big thank you to you…

[SM_Riga]

Please mark @anon73440385 answer as accepted then. You have closed this question as answered, while no answer is accepted. Thanks in advance.

[erAck]

Also, please don’t add Thank Yous as answers, which they are not. Use add comment instead. Thank you