Find average based on if cell is included in mapping tab-CodePudding

I'm looking to find the following Average, or even a Sum, based on if the value located in the column would return a TRUE in a vlookup.

So in the above example, I'm looking to find the average and sum for apple and orange, based on me looking it up based on the word test.

CodePudding user response：

For average try-

=AVERAGE(FILTER(B2:B4,MMULT(--(D2:G4="Test"),SEQUENCE(COLUMNS(D2:G4)))))

For sum try-

=SUM(FILTER(B2:B4,MMULT(--(D2:G4="Test"),SEQUENCE(COLUMNS(D2:G4)))))

CodePudding user response：

For Larger Data you may need this.