Index with small limitations

[mbarbera83]

Hi all.
I am trying to create a list of products by brand, using the Index function with Small, but it seems that my data base is too big for the memory. when i index only to row 21, then it works, but when i take it to row 1500 which I need, i get an error

=IF(ROWS(E$3:E3)>E$2,"",INDEX($A$2:$A$21,SMALL(IF($B$2:$B$21=E$1,ROW($B$2:$B$21)-ROW($B$2)+1),ROWS(E$3:E3))))

is there any way to get around this?

<tbody>
</tbody>

Product	Brand			Brand1	Brand2
product1	Brand1			=count Brand1 in column B	=count Brand2 in column
product2	Brand2			FUNCTION GOES HERE
product3	Brand1

<tbody>
</tbody>

 

[mbarbera83]

No.



See the workbook that implements the formula set up: https://dl.dropboxusercontent.com/u/65698317/mbarbera83 brand sublists.xlsx

THANKS A MILLION.

I noted that the actual brands that i was looking up, had "_" characters in, but when i removed these for pure text based lookups, then it worked, that is why it worked for the example, but when i put the actual data in, it did not work.

thanks for the code and the help!!

 

[Aladin Akyurek]

THANKS A MILLION.

I noted that the actual brands that i was looking up, had "_" characters in, but when i removed these for pure text based lookups, then it worked, that is why it worked for the example, but when i put the actual data in, it did not work.

thanks for the code and the help!!

I assume you are using the formula I set up for you...

 

[mbarbera83]

I assume you are using the formula I set up for you...

i am thanks.
now knowing that it was the "_"character causing the problem - is there a reason why i should use your given formula, over my original one.

yours works, so not going to change it - more for my understanding - where there limits/potential problems with my original code?

 

[Aladin Akyurek]

i am thanks.
now knowing that it was the "_"character causing the problem - is there a reason why i should use your given formula, over my original one.

yours works, so not going to change it - more for my understanding - where there limits/potential problems with my original code?

At a closer look, I see the formulas are the same. I missed I guess your count specification.