VlookUp Multiple Matches & Display Labels

[Ravey]

Hello,
I have data with over 50K rows.
I'm trying categorize this data for analysis.
The data looks like this.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Orange_Shirt_P[/TD]
[TD]$50[/TD]
[/TR]
[TR]
[TD]Orange_Shirt_C[/TD]
[TD]$40[/TD]
[/TR]
[TR]
[TD]Orange_Shirt_P[/TD]
[TD]$10[/TD]
[/TR]
[TR]
[TD]Orange_Shirt_C[/TD]
[TD]$5[/TD]
[/TR]
[TR]
[TD]some misc data here[/TD]
[TD]$70[/TD]
[/TR]
[TR]
[TD]Black_Shirt_P[/TD]
[TD]$20[/TD]
[/TR]
[TR]
[TD]Black_Shirt_C[/TD]
[TD]$19[/TD]
[/TR]
[TR]
[TD]Black_Shirt_P[/TD]
[TD]$12[/TD]
[/TR]
[TR]
[TD]Black_Shirt_C[/TD]
[TD]$5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




I want to categorize the higher prices together and the lower prices together.

[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]Orange_Shirt_P[/TD]
[TD]$50[/TD]
[TD]High P[/TD]
[/TR]
[TR]
[TD]Orange_Shirt_C[/TD]
[TD]$40[/TD]
[TD]High C[/TD]
[/TR]
[TR]
[TD]Orange_Shirt_P[/TD]
[TD]$10[/TD]
[TD]Low P[/TD]
[/TR]
[TR]
[TD]Orange_Shirt_C[/TD]
[TD]$5[/TD]
[TD]Low C[/TD]
[/TR]
[TR]
[TD]Some crazy data here[/TD]
[TD]$70[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]Black_Shirt_P[/TD]
[TD]$20[/TD]
[TD]High P[/TD]
[/TR]
[TR]
[TD]Black_Shirt_C[/TD]
[TD]$19[/TD]
[TD]High C[/TD]
[/TR]
[TR]
[TD]More Crazy data here[/TD]
[TD]$500[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]Black_Shirt_P[/TD]
[TD]$12[/TD]
[TD]Low P[/TD]
[/TR]
[TR]
[TD]Black_Shirt_C[/TD]
[TD]$5[/TD]
[TD]Low C[/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

You could do this very simply by using a Pivot Table...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]Item[/td][td]Price[/td][td][/td][td]Row Labels[/td][td]Max of Price[/td][td]Min of Price[/td][/tr]

[tr][td]

2​

[/td][td]Orange_Shirt_P[/td][td]$50 [/td][td][/td][td]Black_Shirt_C[/td][td]

19​

[/td][td]

5​

[/td][/tr]

[tr][td]

3​

[/td][td]Orange_Shirt_C[/td][td]$40 [/td][td][/td][td]Black_Shirt_P[/td][td]

20​

[/td][td]

12​

[/td][/tr]

[tr][td]

4​

[/td][td]Orange_Shirt_P[/td][td]$10 [/td][td][/td][td]Orange_Shirt_C[/td][td]

40​

[/td][td]

5​

[/td][/tr]

[tr][td]

5​

[/td][td]Orange_Shirt_C[/td][td]$5 [/td][td][/td][td]Orange_Shirt_P[/td][td]

50​

[/td][td]

10​

[/td][/tr]

[tr][td]

6​

[/td][td]some misc data here[/td][td]$70 [/td][td][/td][td]some misc data here[/td][td]

70​

[/td][td]

70​

[/td][/tr]

[tr][td]

7​

[/td][td]Black_Shirt_P[/td][td]$20 [/td][td][/td][td]Grand Total[/td][td]

70​

[/td][td]

5​

[/td][/tr]

[tr][td]

8​

[/td][td]Black_Shirt_C[/td][td]$19 [/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]Black_Shirt_P[/td][td]$12 [/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]Black_Shirt_C[/td][td]$5 [/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
I used Item as Column label
Then I inserted Price twice, and set the 1st up as Max and the 2nd up as Min (you could add a 3rd for avg if you wanted)