This may be asked before, but I was unable to locate any.
I'm having a pivot table in Excel file, with two data fields in Rows and a Value field.
Rows -> Country, Product Values -> Sum of Amount
The data appears like below:
| Country | Products | Amount |
|---|---|---|
| France | phone | 1000 |
| Franc | earpod | 500 |
| France | mouse | 150 |
| Canada | phone | 1000 |
| Canada | chip | 500 |
| Canada | keypad | 150 |
I added a dropdown on another sheet for Country names. I want to show a product snapshot (product names and their amount) when a country is selected.
Dropdown -> France Snapshot
| Product | Amount |
|---|---|
| phone | 1000 |
| earpod | 500 |
| mouse | 150 |
Dropdown -> Canada Snapshot
| Product | Amount |
|---|---|
| phone | 1000 |
| chip | 500 |
| keypad | 150 |
Can anyone please suggest what could be the EXCEL Formula (or expression) to fetch this array of values?
I tried Index and match but it failed because France is at multiple indices.