Forum Discussion

apo114's avatar
apo114
Copper Contributor
Mar 12, 2022

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...
excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Mar 12, 2022

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

apo114's avatar
apo114
Copper Contributor
Mar 12, 2022

PeterBartholomew1 

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

Thanks in advance!

Apo

  • apo114's avatar
    apo114
    Copper Contributor
    Mar 12, 2022

    apo114 

    Corrected..

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Mar 12, 2022

      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.

Resources