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

      • apo114's avatar
        apo114
        Copper Contributor
        Mar 14, 2022
        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....

Resources