CODEWITHSUNDEEP
excelexcel-formulaexcel-2010worksheet-function
Our Man in Bananas
Our Man in Bananas

Reputation: 5981

INDEX & MATCH issue

I have lists on 2 sheets in Excel. On my customers sheet I have this information:

 Cus    Name
 352445 Mr A Person
 352445 Mrs B Wife
 352448 Mr B Someone
 352448 Mr Another Guy
 352448 Mr Who What
 352448 Mr Tall Man
 352448 Mr Big Man
 352448 Mr Yet Another
 352450 Mr Top Dude
 352450 Mr Bad Dude
 352450 Mr Foreign Guy
 352452 Mr Local Man
 352452 Mrs High Scorer

On my target data sheet I have a list of customer numbers:

Cust
352437
352438
352438
352445
352445
352445
352448
352448
352448
352448
352448
352448
352450
352450
352450
352452
352452
352452
352452

I have been trying to use INDEX & MATCH to populate the names from the customer sheet into my target sheet

so I tried:

=MATCH(A2,'Participants Data'!$A$2:$A$674,1)

which puts 1 for 352437, 2 for all instances of 352438,3 for 352445, 4 for 352448

Then I tried using INDEX('Participants Data'!$B$2:$B$674,MATCH(A2,'Participants Data'!$A$2:$A$674,0)) and copying that down.

The problem is that I get this:

Cust    Name
352445  Mr A Person
352445  Mr A Person
352445  Mr A Person
352448  Mr B Someone
352448  Mr B Someone
352448  Mr B Someone
352448  Mr B Someone

...

when it should be (or rather, what I am trying to achieve): 

 352445 Mr A Person
 352445 Mrs B Wife
 352448 Mr B Someone
 352448 Mr Another Guy
 352448 Mr Who What
 352448 Mr Tall Man
 352448 Mr Big Man
 352448 Mr Yet Another

so what am I doing wrong in my INDEX & MATCH ?

Upvotes: 1

Views: 184

Answers (1)

BrakNicku
BrakNicku

Reputation: 5991

To find Nth occurrence of a value in a table, you can can use the following array formula (enter in A2, confirm with Ctrl+shift+Enter, and fill the rest of the range):

 =INDEX(ColB,SMALL(IF(ColA=A2,ROW(ColA)-MIN(ROW(ColA))+1,1E+100),COUNTIF($A$2:A2,A2)))

Where ColA and ColB are the ranges in customer sheet. COUNTIF part calculates N.

Upvotes: 2

Related Questions