Error Message #Value! in 2 formulas

Hello,

Pleas help with a formula with 2 vertical and one horizontal lookup.

My data file is in the sheet "QB_PL" and a report table in "Report" one.
I built these 2 formulas and both give me an error message "#Value!".

=INDEX(QB_PL!C2:AB38,MATCH(Report!B9&D4,QB_PL!B2:B37&QB_PL!A2:A37,0),MATCH(Report!B2,QB_PL!C1:AB1,0))

=SUMIFS(QB_PL!C2:AB37,QB_PL!C1:AB1,Report!B2,QB_PL!B2:B37,
Report!B9,QB_PL!A2:A37,Report!E4)

I checked formatting many times and no success...

The first one using index needs to be entered as an array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

The sumifs won't work at all because it requires the Sum Range to be of Equal dimensions to each criteria range.

Thank you-) the result changed into "N/A"...

The #N/A could come from 2 places (3 really, the first match is essentially 2 matches concatenated together)

Track down which one(s) is failing.
Separe each match into it's own cell.

=MATCH(Report!B2,QB_PL!C1:AB1,0)
=MATCH(Report!B9&D4,QB_PL!B2:B37&QB_PL!A2:A37,0)

Which one is #N/A ?
If it's the one concatenating, then break it down further
=MATCH(Report!B9,QB_PL!B2:B37,0)
=MATCH(D4,QB_PL!A2:A37,0) <- I might guess that D4 needs to also reference the sheet, same as B9.. Report!D4

Hello Jonmo1,

Thank you very much for your help. With your help I resolved the problem. Much appreciated-

You're welcome.