Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

# Array function

Copper Contributor

# Array function

I have a data set with multiple columns and I want to extract certain data into an array. See this example.

 A B C D 1 Salesperson Car Type Number Sold Unit Price 2 Barnhill Sedan 5 2200 3 Barnhill Coupe 4 1800 4 Ingle Sedan 6 2300 5 Ingle Coupe 8 1700 6 Jordan Sedan 3 2000 7 Jordan Coupe 5 1600 8 Pica Sedan 9 2150 9 Pica Coupe 5 1950 10 Sanchez Sedan 6 2250 11 Sanchez Coupe 8 2000

I want to extract an list showing which salesperson sold 5 units so the results would appear like this.

 Barnhill Jordan Pica

What combination of formulas will do this?

4 Replies

# Re: Array function

=FILTER(A2:A11, C2:C11=5, "")

# Re: Array function

Here are some variations on the formula given you  by @Hans Vogelaar , to give an idea of how slightly different questions could be asked of the same data. See the attached file for these examples.

Who sold exactly five units?

``=FILTER(A2:A11,C2:C11=5,"")``

Who sold five or more units? The UNIQUE function is added here because some names would appear twice; Sanchez, for example, sold 6 sedans and 8 coupes.

``=UNIQUE(FILTER(A2:A11,C2:C11>=5,""))``

Who sold five or more Sedans?

``=FILTER(A2:A11,(C2:C11>=5)*(B2:B11="Sedan"),"")``

Who sold five or more Coupes?

``=FILTER(A2:A11,(C2:C11>=5)*(B2:B11="Coupe"),"")``

There are more variations on these formulas, depending on what the question is.

# Re: Array function

Thanks works great

Thanks