add count of elements within array formula (w/o having 2 refer 2 a helper colum)

[becha]

hi all. hope anyone has an answer for this particular matter


suppose in range a1 thru a10 there are elements (product names) that repeat in a random fashion and in range b1 thru b10 there are numeric elements that correspond to each product name in the prior mentioned range


i added this 2 column array formula in cell c1 which allows me to 'extract' specific product name with its correlated numeric values...


=if(a1:a10="product name",choose({1,2},a1:a10,b1:b10),"")


how could i add in the output of the first column, (the product name) resulting from the array formula,...the inclusion (maybe thru the & operator) of the corresponding ocurrance number of each name (like item1, item 2, item3, etc)???


thanks so much for your help

if youd like to see the spreadsheet let me know the sharesite url, and ill be glad to oblige

 

[Hui]

Becha


This doesn't do anything for me apart from return the Lookup value and the corresponding value when they match.


What are you actually trying to achieve ?

 

[becha]

hi hui

imagine this would be the result of the array formula


col#1 col#2

motor 1000

motor 5000

motor 10000

motor 30000


what id like to build into the formula is the ability to include the ocurrance number of the lookup values...like this...


col#1 col#2

motor1 1000

motor2 5000

motor3 10000

motor4 30000


is there any way to achieve this w/o having to resort to a helper column??


thanks hui

 

[Luke M]

Assumptions:

Col 1 is actually column C, col 2 is actually column D, value to find is in E1


Formula in C1:

=IF(COUNTIF(A:A,$E$1)<ROWS($A$1:A1),"",$E$1&ROW(A1))


Formula in D1 (array formula):

=IF(C1="","",INDEX(B:B,SMALL(IF(A$1:A$10=$E$1,ROW(A$1:A$10)),ROW(A1))))


Copy down as needed.