Forum Discussion
Using INDEX with SUMIFS?
In one spreadsheet, titled "Code Sheet", in column "A" I have the names of health codes. In column "B" I have point values that correspond with the codes in column "A" (1-5points). In a second spread...
For me the start was to use XLOOKUP to convert the codes that may appear anywhere within a range into the equivalent values. From there the values may be aggregated any way you choose. For example
= LET(
value2020, XLOOKUP(data2020, Codes, Values, 0),
BYROW(value2020, LAMBDA(x, SUM(x)))
)Using insider beta that reduces to
= LET(
value2020, XLOOKUP(data2020, Codes, Values, 0),
BYROW(value2020, SUM)
)PeterBartholomew1 are Value2020 and data2020 the datasheets in your formula? If not that are they functioning as?
I defined the name 'data2020' to be the entire range that contains data on sheet 2020. The formula examines every cell of the range to determine whether it can be found in the list and, if so, returns a value 'value2020' that in the numerical equivalent of the health code. From there I have summed the rows.
- This is what I have put as my equation:
=LET(value2022, XLOOKUP(data2022, 'Code Sheet'!$A$2:$A$144, 'Code Sheet'!$B$2:$B$144, 0), BYROW(value2022, SUM))
It comes back as a name error. What do I do from here?
