• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup with mulitple criteria

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

  • New Microsoft Excel Worksheet (2).xlsx
    8.4 KB · Views: 5
63496

Formula solution.

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

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

Regards
Bosco
 
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.
 
Paste in B4 and drag over and down
=SUMPRODUCT(($J$4:$J$12=$A4)*($L$4:$L$12=B$3),$M$4:$M$12)
 
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

  • Unpivotting array.xlsx
    16.6 KB · Views: 3
Last edited:
Back
Top