I've been trying to understand the array vlookup approach and have not been successful. If I have a list of item codes and I want to return a list of the unique items horizontally
If starting in B2, I enter this table:
<tbody>
</tbody>
Then I want to list out, horizontally, the Coffee orders, I use the array formula:
{=IFERROR(INDEX($B$2:$C$9,SMALL(IF($B$2:$B$9=$E2,ROW($B$2:$B$9)-1),COLUMNS($F2:F2)),2),"")}
Where cell E2 is Coffee (or could be one of the other categories).
My issue is that there are a ton of orders and I would like to edit this so that I only return unique values.
It takes all of my excel skill and brain power to understand the equation I wrote, if someone can offer the solution for unique values and also a brief explanation as to how it works, it would be greatly appreciated. Thanks in advance!
If starting in B2, I enter this table:
Category | Item |
Coffee | Espresso |
Coffee | Espresso |
Coffee | Cappuccino |
Soda | Coke |
Other | Water |
Coffee | Espresso |
Coffee | Americano |
<tbody>
</tbody>
Then I want to list out, horizontally, the Coffee orders, I use the array formula:
{=IFERROR(INDEX($B$2:$C$9,SMALL(IF($B$2:$B$9=$E2,ROW($B$2:$B$9)-1),COLUMNS($F2:F2)),2),"")}
Where cell E2 is Coffee (or could be one of the other categories).
My issue is that there are a ton of orders and I would like to edit this so that I only return unique values.
It takes all of my excel skill and brain power to understand the equation I wrote, if someone can offer the solution for unique values and also a brief explanation as to how it works, it would be greatly appreciated. Thanks in advance!