• 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


  • 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


Excel Ninja

Formula solution.

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




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

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) )
= 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!


Last edited: