could you guys help me with a project. I was able to find a solution for my problem and the formula looks like this:

=IFERROR(VLOOKUP(E4;A8:B13;2;FALSE);0) IFERROR(VLOOKUP(F4;A8:B13;2;FALSE);0) IFERROR(VLOOKUP(G4;A8:B13;2;FALSE);0)

I have a category (e.g. Fruits) and need to import a sheet with different kind of fruits and non fruits. I use keywords which define what is a fruit and what not. I need to SUM all values which match to a keyword. My formula works but it will be more and more work when i need to add more keywords.

Are there a better way to realise this?

I build this example sheet for better understanding : )

you can even use E4:4 or E4:G5 or E4:5

row-wise it would be:

=INDEX(BYROW(E4:G5; LAMBDA(x; SUM(IFNA(VLOOKUP(x; A8:B13; 2; ))))))

CodePudding user response：

use this

=ArrayFormula(SUM( IFERROR( VLOOKUP(E4:G4;A9:B;2;0))))

Usign sumif

=ArrayFormula(SUM(SUMIF(A9:A;"="&E4:G4;B9:B)))

Xlookup

=ArrayFormula(SUM(XLOOKUP(E4:G4;A9:A14;B9:B14;"";1)))

CodePudding user response：

Us XLOOUP instead of IFERROR(VLOOKUP()) can shortens the formula.

=SUMPRODUCT(XLOOKUP($E4:4,$A$9:$A,$B$9:$B,0))