Vlookup with multiple criteria

[Liberty Prime]

Hi there!

I have another question to ask to the Excel Masters:

I would like to work with a Vlookup with three different criteria (X, Y and date), and I'm a bit stuck when I tried to use it in Google Spreadsheets...

My goal was to avoid creating extra side columns with the values X, Y and Date concatenated in order to make a single criteria to look for (mainly because there are already some side columns in use with a SUMIFS). The funny thing is that by following the advice of this link somehow it has worked fine in Excel.

https://chandoo.org/wp/2014/10/28/multi-condition-vlookup/

The formula in excel looks like this:

[INDENT][FONT=arial]{=VLOOKUP([COLOR=#000000][COLOR=#F7981D]A8[/COLOR]&[COLOR=#7E3794]B8[/COLOR]&([/COLOR][COLOR=#000000]DATE[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]YEAR[/COLOR][COLOR=#000000]([/COLOR][COLOR=#11A9CC]Input!$B$4[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000]MONTH[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]DATEVALUE[/COLOR][COLOR=#000000]([/COLOR][COLOR=#11A9CC]Input!$B$4[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000],[/COLOR][COLOR=#A61D4C]$C$2[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000])[/COLOR],CHOOSE({1,2},Input!A:A&[COLOR=#4285F4]Input!A:A[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#F4B400]Input!B:B[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#65B045]Input!C:C[/COLOR][COLOR=#000000],[/COLOR][COLOR=#795548]Input!J:J[/COLOR]),2,0)}[/FONT][/INDENT]

But the troubles come when I pasted it in Google spreadsheets and I received an error as output. I assume that it must be something related with the syntax, as when I hit the combination CTRL + Shift + Enter it changes to "Arrayformula".

https://support.google.com/docs/answer/3093275?hl=de

[INDENT][FONT=arial][COLOR=#000000]=[/COLOR][COLOR=#000000]ArrayFormula[/COLOR][COLOR=#000000]([/COLOR]VLOOKUP[COLOR=#000000]([/COLOR][COLOR=#F7981D]A8[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#7E3794]B8[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]DATE[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]YEAR[/COLOR][COLOR=#000000]([/COLOR][COLOR=#11A9CC]Input!$B$4[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000]MONTH[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]DATEVALUE[/COLOR][COLOR=#000000]([/COLOR][COLOR=#11A9CC]Input!$B$4[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000],[/COLOR][COLOR=#A61D4C]$C$2[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000],[/COLOR]CHOOSE[COLOR=#000000]([/COLOR][COLOR=#000000]{[/COLOR][COLOR=#1155CC]1[/COLOR][COLOR=#000000],[/COLOR][COLOR=#1155CC]2[/COLOR][COLOR=#000000]}[/COLOR][COLOR=#000000],[/COLOR][COLOR=#4285F4]Input!A:A[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#F4B400]Input!B:B[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#65B045]Input!C:C[/COLOR][COLOR=#000000],[/COLOR][COLOR=#795548]Input!J:J[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000],[/COLOR][COLOR=#1155CC]2[/COLOR][COLOR=#000000],[/COLOR][COLOR=#1155CC]0[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000])[/COLOR][/FONT][/INDENT]

It may also be something related with the Choose formula, as it also seems that it has a different syntax compared to the one from Excel.

https://support.google.com/docs/answer/3093371?hl=en

If I replace the end "...,2,0))" by "...,1,0))" I get the concatenated values to look for, which means that somehow the formula is working and proves that I have no idea what it's going on the Google Spreadsheets

Can somebody please help me with this?

Thanks in advance!

 

[Liberty Prime]

Anyone?

 

[Liberty Prime]

Okay, after a lot of testing and looking in google for an answer, I finally figured out how to achieve the desired result with a Index - Match function:

http://professor-excel.com/multi-condition-lookups-vlookup-index/

In case it also helps someone, the formula looks like that:

=INDEX(Input!J:J,MATCH(A9&B9&(DATE(YEAR(Input!$B$4),MONTH(DATEVALUE(Input!$B$4)),$C$2)),Input!A:A&Input!B:B&Input!C:C,0))

The thread can be ignored as it's solved.