Excel formula is not available in numbers

That is an Excel "array" formula, a feature Numbers does not have. Array formulas can do the work of an entire column of formulas within the one cell. Numbers needs a column of formulas. Here is one of several possible formula solutions:

Column I will be hidden when you are done so ignore all the error triangles in the screenshot

Cell I1 has a 0 typed in it, or you can leave it blank

I2 =MATCH($G$2,OFFSET(A$1,I1,0,ROWS(A)−I1,1),0)+I1

H2 =IFERROR(OFFSET(B$1,I2−1,0),"")

Fill down with the formulas to complete the columns

Hide column I when it is all set up and working.

Hi zeisshyun,

I don't understand the Excel formula.

Perhaps a Filter will work for you.

Insert another Table ("Filter") with a Pop-Up Menu:

Insert a helper Column (D) in your data table.

Formula in D2 is IF(Filter::A$1="Choose a Team","Show",IF(Filter::A$1=B2,"Show",""))

Apply a filter:

Choose a Team:

Done!

Regards,

Ian.

Nice to see Pivot Table used!

There is also 'Categories'.

Click the column letter for the 'Team' column and from the dropdown choose Add Category For 'Team'.

But unlike Pivot Table that can alter the order in the original table.

SG

Or... a Pivot table:

Please call back with questions.

Ian.