Vlookup with mulitple criteria

[Arup Dutta Chowdhury]

I have an excel sheet where employee code name and year wise score is given.Now I want to know what is the yearly employee code wise score. For example what is the score of one employee (A001) year wise in 2017,2018 and 2019.

 

[vletm]

Arup Dutta Chowdhury
without Vlookup ...

 

[Arup Dutta Chowdhury]

I do not want to use the pivot table. Is there any way to do it with vlookup or any other formula.

 

[bosco_yip]

Formula solution.

In B4, copied across to D4 and all copied down :

=SUMIFS($M:$M,$J:$J,$A4,$L:$L,B$3)

Regards
Bosco

 

[vletm]

Arup Dutta Chowdhury
I won't use formulas if could use something without those; why to copy & paste ...?
Yes ... there are many possibilities as above or
just with Your thread,
for cell B4 =SUMIFS($M$4:$M$12,$J$4:$J$12,$A4,$L$4:$L$12,B$3)
>>> copy and paste as needed.

 

[Juniad]

Paste in B4 and drag over and down
=SUMPRODUCT(($J$4:$J$12=$A4)*($L$4:$L$12=B$3),$M$4:$M$12)

 

[Peter Bartholomew]

Spoilt for choice using dynamic arrays! Treating the source data as a DB Table, matches can be picked up wherever they occur using
= SUMIFS( Table1[Score], Table1[C0de], C0de, Table1[Year], Yr )
Alternatively the problem can be treated as one of array storage and the data returned by index
= INDEX( Table1[Score], TRANSPOSE(SEQUENCE( 3, 3 )) )
or, to avoid SEQUENCE which needs Office 365 monthly
= INDEX( Score, {1;2;3} + 3*{0,1,2} )

If one is content to return the result cell by cell, rather than as an array
= FILTER( Score, (Year=@Yr)*(C0des=@C0de) )
or
= TRANSPOSE( FILTER(Table1[Score], Table1[C0de]=@C0de) )
would return a single row of the array.

Edit. Wow, the use of the word 'Code' in square brackets can reduce the post to wreckage!