Mar 12 2022 06:01 AM
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 defined in a table on another sheet.
c. How do I name them automatically by reference this type of categorization on the table for subsequent lines?
Mar 12 2022 08:58 AM
This is a guess at what you require but, if the values 'typeA' and 'typeB' are looked up as the row and column headers of the 'category' table, categories may be returned using
= INDEX(category, XMATCH(typeA, listA, -1), XMATCH(typeB, listB, -1))
Mar 12 2022 11:40 AM
Can you solve the issue under the "TEAM", so that I know what it is under the conditions below?
Thanks in advance!
Apo
Mar 12 2022 01:05 PM
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.
Mar 13 2022 10:01 PM
Mar 14 2022 05:19 AM
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.
Mar 17 2022 12:58 AM
1. The product works on my office 365 at home, which I evolved to fit more data in.
2. Unfortunately, my office 2016 professional plus, cannot comply with the formula, and whatever change I am doing, it does not respond.
3. Attached you will find the excel options/functions snapshot, where I may have to tweek a bit.
4. I would appreciate if you took a look on how I can work this on office 2016 pro plus.
Mar 17 2022 03:22 AM
I don't like defined names and other formulas that reference empty cells, so I have converted everything (except the 365 current channel dynamic array formula) to Tables and structured references.
Mar 17 2022 03:37 AM
The first change or addition I am trying to do, gives me the attached result. Is there any incompatibility issue behind this?
Mar 17 2022 04:04 AM
The columns with the #Name! error were neve intended to work with Office 2016 so are not a problem. The blank column is an issue though and, possibly, arises from the intersection operator '@' not converting to old style formulas. Try replacing these references by table structured references
= IFERROR(
LOOKUP(
1,
1/([@POINTS]>=A_lower)/([@POINTS]<=A_upper)/([@CASH]>=B_lower)/([@CASH]<=B_upper),
TEAM)
,""
)
in which '@POINTS' is replaced by '[@POINTS]'.
Mar 17 2022 01:43 PM
Peter,
1. I applied your advice in the original spreadsheet that you sent.
2. It works just fine.
3. On the other hand, when I try to apply the process on a new excel file, It does not work.
4. I have encountered this again with pivot tables from the 365 to the version of office before the 2016 (I don't quite remember exactly which it was).
5. I think that as I use this type of formula more often, it will be easier to apply.
Thanks a lot for the effort and help. It is much appreciated!
Apr 29 2022 10:52 AM
Team,