Mar 12 2022 06:01 AM
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 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),"") ) )
= 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 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
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