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?

	A​	B​	C​	D​	E​
1​	Names​	Value​	Names​	Value​	Formula​
2​	Anthony​	10​	John​	20​
3​	John​	11​	John​	21​
4​	Mary​	12​	Mary​	22​
5​	Anthony​	13​	Anthony​	23​
6​	John​	14​	Anthony​	24​
7​	Mary​	15​	Mary​	25​
8​	Anthony​	16​	John​	26​	17​
9​	John​	17​	John​	27​
10​	Anthony​	18​	Mary​	28​	19​
11​	Mary​	19​	Anthony​	29​	16​
12​	John​	20​	John​	30​
13​	Mary​	21​	John​	31​
14​	Anthony​	22​	John​	32​
15​	John​	23​	Anthony​	33​
16​	Mary​	24​	Anthony​	34​

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.

Anthony	10	John	20	11
John	11	John	21	14
Mary	12	Mary	22	12
Anthony	13	Anthony	23	13
John	14	Anthony	24	16
Mary	15	Mary	25	15
Anthony	16	John	26	17
John	17	John	27	20
Anthony	18	Mary	28	19
Mary	19	Anthony	29	16
John	20	John	30	23
Mary	21	John	31
Anthony	22	John	32
John	23	Anthony	33	18
Mary	24	Anthony	34	22

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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.