XLOOKUP with multiple search criteria and multiple return values

[Cuni]

Hi,

See attached sheet. Is this lookup possible?

[dosydos]

close try this instead:

=CHOOSE({1,2},XLOOKUP(D2,M:M,N:N),XLOOKUP(D2,O:O,P:P))

[Cuni]

I'm getting #SPILL!

[Cuni]

close try this instead:

=CHOOSE({1,2},XLOOKUP(D2,M:M,N:N),XLOOKUP(D2,O:O,P:P))

I'm getting #SPILL!

[CARACALLA]

E2=SUMPRODUCT(($M$2:$O$5=D2)*(N(+($N$2:$P$5))))

Copy down

[dosydos]

if you are getting spill error that means there is data below or to the side of it so it can't show all results. just delete the excess data and you should be good

[Cuni]

if you are getting spill error that means there is data below or to the side of it so it can't show all results. just delete the excess data and you should be good

Thanks I will check tomorrow again.

[Cuni]

E2=SUMPRODUCT(($M$2:$O$5=D2)*(N(+($N$2:$P$5))))

Copy down

Thank you. I find this one difficult to grasp.

I prefer XLOOKUP with CHOOSE provided I can make it work

[Cuni]

Attachment 802301
Not able to make it work...

[Cuni]

Attachment 802302
Not even this is working. It should who 10 in E2 and 22 in F2...

[AliGW]

Provide a workbook with your expected results mocked up MANUALLY, NOT a non-working formula.

[Cuni]

Here is the solution I'm looking for. I put in the values manually but would like to solve it with formula starting in E2.

[AliGW]

One way:

=SUMPRODUCT(($M$2:$M$3=D2)*($N$2:$N$3))+SUMPRODUCT(($O$2:$O$3=D2)*($P$2:$P$3))

[AliGW]

Another (365) way:

=LET(v,VSTACK($M$2:$N$3,$O$2:$P$3),VLOOKUP(D2,v,2,0))

[CARACALLA]

E2=SUMIF($M$2:$O$3,$D2,$N$2:$P$3)

Copy down

[Cuni]

Thank you all!

[AliGW]

Glad to have helped.