Forum Discussion
The Lambda function and its reliance on the Excel Name Manager
George, names in Excel are connected, but these are different things. You may use LAMBDA only with local names without global names in name manager manager, if only to name lambda function.
Names with relative references are always works such way as you described, independently on lambdas. If you stay in row #3 and define the name as reference on $C2, in any cell in second row name will show $C1, if you stay in first row it shows $C1048576 since you are out of grid range and reference reverted on the last row of the grid.
Lamdas are oriented to work with arrays. In your case that could be range $C$2:$C$50 or so named as lastName and within lambda you calculate where is data for week 6 depends on your data structure logic, and return desired value.
You don't need relative cell references working with arrays and lambdas.