Forum Discussion
Using INDEX with SUMIFS?
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?The key to my formulas is the defined names used to identify data ranges (highlighted in yellow in the workbook). That and the fact that I always use 365 functionality.