Function Help

[Harry1974]

Hi Everyone,

I'm stumped on how to make this work.  I have 5 category ranges that need to be looked up to yield the appropriate answer.  Help please...

[#2 · Accepted solution]

@Harry1974 

In D14:

=INDEX($A$4:$A$9,MATCH(C14,$B$4:$B$9))

or

=LOOKUP(C14,$B$4:$B$9,$A$4:$A$9)

If you have Excel in Microsoft 365, you can also use

=XLOOKUP(C14,$B$4:$B$9,$A$4:$A$9,"",-1)

Fill down.

[#3]

@Harry1974 

The total points are given by

= SUMIFS(PtsGained, Event, "<="&Event)

Putting this together with an XLOOKUP could give

= LET(
  ptsTotal, SUMIFS(PtsGained, Event, "<="&Event),
  status, XLOOKUP(ptsTotal, threshold, level,"",-1),
  IF({1,0}, ptsTotal, status) )

which returns both the total points and status columns from a single formula written in cell C14.

[#4]

Thank you so much for the help, that worked just fine.

[#5]

Thank you again for the help, that worked.

[#6]

Thanks that worked