• 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

bosco_yip

Excel Ninja
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
 

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

Last edited:
Top