INDEX&MATCH with multiple conditions doesn't work for string values

[samto22]

Hello!

I'm in stuck finding solution how to get data that match multiple conditions using INDEX&MATCH array formula. Please refer to attached file. Formula works if ID is number, but do not work if ID contains letter or is string.

Thanks in advance!

 

[Deepak]

With Ctrl+Shift+Enter

=INDEX($C$6:$D$10,MATCH(G7,$C$6:$C$10,0),MATCH(YEAR($H$6),YEAR($C$6:$D$6),0))

 

[Chihiro]

Try this
For H7: =INDEX($D$7:$D$10,MATCH(G7&YEAR($H$6),$C$7:$C$10&YEAR($D$6),0))

Enter as array (CSE), and copy down.

 

[r1c1]

Welcome to Chandoo.org forums and thanks for posting your question.

I am not sure why you are using such long and complex formula. You can use this instead.

=IF(YEAR(H$6)=YEAR(D$6),INDEX($D$7:$D$9,MATCH(G7,$C$7:$C$9,0)),"")

or even simpler,

=IF(YEAR(H$6)=YEAR(D$6),VLOOKUP(G7,$C$7:$D$9,2,false),"")

 

[Hui]

Have I missed something because
=VLOOKUP(G7,$C$7:$D$9,2)
is even simpler

If you want to add a date field I would add a separate/new Column to Table 1 with dates, instead of having it in the header?

 

[samto22]

Dear all,

Thank you all for answers. They really helped(just wanted to find out why formula works when ID is number but do not work if ID contains letter or is string), but things are more complicated. I'm attaching a fragment from my tables, with real formula I use. Tried to apply all yours answers for yellow marked ID bu not successful. Please review and let me know what I'm doing wrong.

Thanks!

 

[Deepak]

paste in E13

=SUMIFS(ImportTable[[Amount]:[Amount]],ImportTable[[Profit centre]:[Profit centre]],$D$10,ImportTable[[Month]:[Month]],MONTH(E$11),ImportTable[[Year]:[Year]],YEAR(E$11),ImportTable[[GL Number]:[GL Number]],$D13)


& drag right/down

 

[Deepak]

It's a basic mathematics that you can only do the calculations with numeric values not string type

2+2 = 4
2+2A = error

 

[Hui]

E13:
=SUMPRODUCT((ImportTable[Profit centre]=$D$10)*(ImportTable[GL Number]=$D13)*(ImportTable[Year]=YEAR(E$11))*(ImportTable[Month]=TEXT(MONTH(E$11),"0"))*(ImportTable[Amount]))

Copy across and down

You need to be aware that Months column in Import are Text, Not Numbers