USE A NAME FROM A TABLE

Copper Contributor

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?

 

13 Replies

@apo114 

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))

@Peter Bartholomew 

apo114_0-1647113955012.png

Can you solve the issue under the "TEAM", so that I know what it is under the conditions below?

Thanks in advance!

Apo

@apo114 

apo114_0-1647114444518.png

Corrected..

@apo114 

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....

@apo114 

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.

@Peter Bartholomew 

 

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.

@apo114 

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.

@Peter Bartholomew

The first change or addition I am trying to do, gives me the attached result. Is there any incompatibility issue behind this?

@apo114 

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]'.

@Peter Bartholomew 

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!

@apo114 

Team, 

1. Next step is to categorize in the same list the teams into wider teams that they belong to.
2. Additionally, There ia also another wider team tier.
3. Again, the teams are exclusive and the wider teams will also be exclusive as a simple sum of the subteams.
4. For example AEK, AEL1 and AEL 2 belong to the wider team of Central GR, PAO and OSFP belong to South GR, ARIS, PAOK belong to North GR, And AEL 3,4,5 belong to North Belistria. In the next tier, Central GR, south GR and North Gr belong to Gr, while North Belistria belongs to Belistria. Other sub teams of all tiers may pop up.
5. How can I achieve that in the same spreadsheet?
6. Again, I am using excel 2016 at work or 365 at home.
Thanks in advance!
Best Regards!