Expansion on match index formulas

[jarredwyatt]

I currently use excel 2010 at work ( i have attched a file using excel for mac ).

I need to populate the "name" into the G column that match the "stores" in H column to "run 1" and "run 2". The formula works fine until I get a repeat in the number. Example - Store 6 appears in run 1 twice and once in run 2. The problem I get is that when I get to the second 6 or the 3rd 6 it keeps returning the first name (Bill). Can anyone help?

[Pete_UK]

Put this formula in C6:

=IF(B6="","",B6&"_"&COUNTIF(B$6:B6,B6))

and this one in E6:

=IF(D6="","",D6&"_"&COUNTIF(D$6:D6,D6)+COUNTIF(B:B,D6))

and copy both these down to the bottom of your data. Then you can amend the formula in H6 to this:

=IFERROR(IF(I6,INDEX($A$6:$A$14,MATCH(I6&"_"&COUNTIF(I$6:I6,I6),$C$6:$C$14,0)),""),IFERROR(IF(I6,INDEX($A$6:$A$14,MATCH(I6&"_"&COUNTIF(I$6:I6,I6),$E$6:$E$14,0)),""),""))

and copy that down as required.

Hope this helps.

Pete

[jarredwyatt]

Hi Pete,

Thans for your reply. It works great but without sounding ungreatful I was wondering if it was possible to do this without adding in the extra formulas to columns C and E as in my work spreadsheet they have data in them - Iknow i could just add columns and then hide them but i would prefer not if i can get away with it. Also are you able to run me through how this part of the formula works =IF(B6="","",B6&"_"&COUNTIF(B$6:B6,B6)) - I am unfamiliar with what & and _ do.
Thanks

[Glenn Kennedy]

How are you generating the data in column I? Is it being generated from B and D, or in some other way?

[Pete_UK]

Your INDEX/MATCH formula is looking for an exact match, and the way MATCH works means that it will find the FIRST occurrence of any duplicated numbers, so you will always get the first name. My formulae set up a unique reference by adding on a sequential number to the store number - the ampersand symbol is the same as the CONCATENATE function (it joins strings together), and the underscore is there just to separate the two elements.

Hope this helps.

Pete

[jarredwyatt]

It is referencing from another worksheet i.e. "=I6'!worksheet2'

[Glenn Kennedy]

In that case - go with Pete's offering. You could also put the helpers on an entirely different sheet and hide it completely...