# Vlookup with mulitple criteria

#### Arup Dutta Chowdhury

##### Member
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.

#### Attachments

• 8.4 KB Views: 5

#### Attachments

• 12.6 KB Views: 7

#### Arup Dutta Chowdhury

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

#### bosco_yip

##### Excel Ninja

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

##### Excel Ninja
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
for cell B4 `=SUMIFS(\$M\$4:\$M\$12,\$J\$4:\$J\$12,\$A4,\$L\$4:\$L\$12,B\$3)`
>>> copy and paste as needed.

##### Member
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

##### Well-Known Member
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!

#### Attachments

• 16.6 KB Views: 3
Last edited: