How to use column-expression in case of more than ...

eusy1327 · 12-11-2021 08:51 AM

I already know that how to use Match and Index function in google sheet.
But I would like to know in case of more than one critiras in a cell.

The sinario is below.
1 . There is a reference table has fruit name and each price.
2 . Another is a cell has more than one fruit name to selected
(ex. apple , banna )and it is sperated by comma.
3 . finally I want to calculation sum price (apple, banna) through the column-expression.

guide me please~

SkrOYC

In general, I see this case being implemented in two tables. But, I think it should work anyway.
Try

SUM(
  SELECT(
    TABLENAME[PRICES($)],
    IN(
      [ITEM],
      [_THISROW].[SELECTION]
    )
  )
)

How to use column-expression in case of more than one critria in a cell to sum calculate