how to choose best 3 from each category in excel in separate column

 
To choose the best 3 values from each category in Excel and place them in separate columns, you can use a combination of functions like `INDEX`, `MATCH`, and `SMALL`. I'll provide a step-by-step guide on how to do this:

Let's assume you have your data in the following format, with categories in column A and values in column B:

|    A     |   B   |
|---------|-------|
| Category1 |  10   |
| Category1 |  15   |
| Category1 |  5    |
| Category1 |  20   |
| Category1 |  8    |
| Category2 |  18   |
| Category2 |  12   |
| Category2 |  25   |
| Category2 |  22   |
| Category2 |  30   |

To extract the top 3 values for each category into separate columns, you can follow these steps:

1. First, you need to create a ranking for each value within its category. In cell C2 (assuming your data starts in row 2), enter the following formula and drag it down for all the rows with data:

```excel
=COUNTIF(A$2:A2, A2)
```

This formula counts how many times the current category has appeared in the range A$2:A2. It will give you a rank for each value within its category.

2. In cell D2, enter the following formula and drag it down:

```excel
=IF(C2<=3, B2, "")
```

This formula will display the value from column B only if its rank (from column C) is 3 or lower. Otherwise, it shows an empty string.

3. Now, you should have values in column D for the top 3 values in each category. However, they are mixed together. To separate them into separate columns, you can use formulas like this:

For the first category, in cell E2, enter the following formula and drag it down:

```excel
=IF(A2="Category1", D2, "")
```

Replace "Category1" with the name of the category you want to extract into this column.

Repeat this step for each of the three categories, creating separate columns for each. This will give you the top 3 values from each category in separate columns.

Here's how your Excel sheet might look after implementing these steps:

|    A     |   B   |  C  |  D  |  E  |  F  |  G  |
|---------|-------|----|----|----|----|----|
| Category1 |  10   | 1  | 10   | 15   | 20   |     |
| Category1 |  15   | 2  | 15   | 10   | 15   |     |
| Category1 |  5    | 3  | 5    | 5    | 8    |     |
| Category1 |  20   | 4  |      |      |      |     |
| Category1 |  8    | 5  |      |      |      |     |
| Category2 |  18   | 1  | 18   |      |      | 30  |
| Category2 |  12   | 2  | 12   |      |      | 25  |
| Category2 |  25   | 3  | 25   |      |      | 22  |
| Category2 |  22   | 4  |      |      |      |     |
| Category2 |  30   | 5  |      |      |      |     |

This setup allows you to extract the top 3 values from each category into separate columns (E, F, G in this example).