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

[![enter image description here](https://i.stack.imgur.com/fS9du.png)](https://i.stack.imgur.com/fS9du.png)

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.

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

```

[![enter image description here](https://i.stack.imgur.com/qSHei.png)](https://i.stack.imgur.com/qSHei.png)
For Larger Data you may need this.

[![Formula Lookup](https://i.stack.imgur.com/xtPW6.png)](https://i.stack.imgur.com/xtPW6.png)