Index match to pick nth figure

[pedexe90]

Hi everyone,

So I have a list of names and figures on A:A, B:B respectively as well as C:C, D:D and I want E:E to match names from C:C to A:A and return B:B figures. The problem is that names are repeated on both lists on many occasions with different figures attributed to them. How do do an index match formula to know to match the third repeated name to the third repeated name on A:A rather than returning the first matched value.

thanks

 

[Aladin Akyurek]

The following gives you the last instance of C2 in the A range...

=LOOKUP(9.99999999999999E+307,1/($A$2:$A$20=C2),$B$2:$B$20)

Does this meet your intend?

 

[pedexe90]

Thanks for the reply.

This gives me the last instance as you said. But, I want a function that knows how many times that name is being repeated in column C:C (i.e. 4th instance) that to look for the nth (4th instance) on the first column and to return the figure from B:B.

so I found this searching the google [FONT=&quot]To get any nth relative values, you can apply the following formula: [/FONT][FONT=&quot]=INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),1))[/FONT][FONT=&quot] + [/FONT]Ctrl[FONT=&quot] + [/FONT]Shift[FONT=&quot] + [/FONT]Enter[FONT=&quot] keys together, this formula will return the first matched value.

[/FONT]But how do I modify this to get what I need.

 

[Marcelo Branco]

Is this what you need?

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Names​

[/td][td]

Value​

[/td][td]

Names​

[/td][td]

Value​

[/td][td]

Formula​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Anthony​

[/td][td]

10​

[/td][td]

John​

[/td][td]

20​

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

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

John​

[/td][td]

11​

[/td][td]

John​

[/td][td]

21​

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

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Mary​

[/td][td]

12​

[/td][td]

Mary​

[/td][td]

22​

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

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Anthony​

[/td][td]

13​

[/td][td]

Anthony​

[/td][td]

23​

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

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

John​

[/td][td]

14​

[/td][td]

Anthony​

[/td][td]

24​

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

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Mary​

[/td][td]

15​

[/td][td]

Mary​

[/td][td]

25​

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

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td="bgcolor:#E6B8B7"]

Anthony​

[/td][td]

16​

[/td][td="bgcolor:#FFFF00"]

John​

[/td][td]

26​

[/td][td]

17​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td="bgcolor:#FFFF00"]

John​

[/td][td]

17​

[/td][td]

John​

[/td][td]

27​

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

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

Anthony​

[/td][td]

18​

[/td][td="bgcolor:#C5D9F1"]

Mary​

[/td][td]

28​

[/td][td]

19​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td="bgcolor:#C5D9F1"]

Mary​

[/td][td]

19​

[/td][td="bgcolor:#E6B8B7"]

Anthony​

[/td][td]

29​

[/td][td]

16​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

John​

[/td][td]

20​

[/td][td]

John​

[/td][td]

30​

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

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

Mary​

[/td][td]

21​

[/td][td]

John​

[/td][td]

31​

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

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

Anthony​

[/td][td]

22​

[/td][td]

John​

[/td][td]

32​

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

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td]

John​

[/td][td]

23​

[/td][td]

Anthony​

[/td][td]

33​

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

[tr][td="bgcolor: #DCE6F1"]

16​

[/td][td]

Mary​

[/td][td]

24​

[/td][td]

Anthony​

[/td][td]

34​

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

Array formula in E2 copied down
=IF(COUNTIF(C$2:C2,C2)=3,INDEX(B$2:B$16,SMALL(IF(A$2:A$16=C2,ROW(A$2:A$16)-ROW(A$2)+1),3)),"")
Ctrl+Shift+enter

M.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=IFERROR(INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),F1)),"not available")

where F1 houses a value like 1 or 7, etc.

 

[pedexe90]

Yes. Except that it should also fill out the other cells.

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Anthony[/TD]
[TD="width: 64, align: right"]10 [/TD]
[TD="width: 64"]John[/TD]
[TD="width: 64, align: right"]20[/TD]
[TD="width: 64, align: right"]11[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]11 [/TD]
[TD]John[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]12[/TD]
[TD] Mary[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Anthony[/TD]
[TD="align: right"]13[/TD]
[TD]Anthony[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]14[/TD]
[TD]Anthony[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]15 [/TD]
[TD]Mary[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Anthony[/TD]
[TD="align: right"]16[/TD]
[TD] John[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]17[/TD]
[TD] John[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Anthony[/TD]
[TD="align: right"]18[/TD]
[TD] Mary[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]19 [/TD]
[TD]Anthony[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]20[/TD]
[TD]John[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]21[/TD]
[TD]John[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Anthony[/TD]
[TD="align: right"]22[/TD]
[TD]John[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]23[/TD]
[TD]Anthony[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]24[/TD]
[TD]Anthony[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]22[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Try

Array formula in E2 copied down
=IFERROR(INDEX(B$2:B$16,SMALL(IF(A$2:A$16=C2,ROW(A$2:A$16)-ROW(A$2)+1),COUNTIF(C$2:C2,C2))),"")
Ctrl+Shift+Enter

M.

 

[pedexe90]

Try

Array formula in E2 copied down
=IFERROR(INDEX(B$2:B$16,SMALL(IF(A$2:A$16=C2,ROW(A$2:A$16)-ROW(A$2)+1),COUNTIF(C$2:C2,C2))),"")
Ctrl+Shift+Enter

M.

That does it. Fantastic and thank you very much.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.