Forum Discussion
USE A NAME FROM A TABLE
1. A line refers to an event. I have 2 conditions: a. 2 cells of the line have 2 different type of numbers b. The combination of those 2 numbers according to their range, puts them in a category as...
What version of Excel do you use? I normally develop for 365.
= MAP(POINTS, CASH,
LAMBDA(pts,usd,
FILTER(TEAM,
(pts>=A_lower)*(pts<=A_upper) *
(usd>=B_lower)*(usd<=B_upper),"")
) )Without Lambda
= FILTER(TEAM,
(@POINTS>=A_lower)*(@POINTS<=A_upper)*
(@CASH>=B_lower)*(@CASH<=B_upper),
"")and copy down. Otherwise
= IFERROR(
LOOKUP(1,
1/(@POINTS>=A_lower)/(@POINTS<=A_upper)
/(@CASH>=B_lower) /(@CASH<=B_upper),
TEAM),
"")and copy down.
- Thank you for the help. The conditions table is the same excel file but in a different worksheet. Moreover, the list, to be updated, I will need to add below the conditions and the results extra cases.Can I drag the formula cell to migrate it below? Do I need to be doing something else in the cells so that I do not lose the formula? I use 365 and office 2016. MANY THANKS anyway....
It doesn't matter where TEAM or the limits are as long as the defined Names are absolute references pointing to the data. The final formula should work with 2016 though the "@" notation is surplus to requirements since 2016 interprets such references as relative by using implicit intersection.